Stats Count Eval If

[IRHM73]

Hi, I wonder whether someone can help me please.

I'm using number the following as part of a query to extract data from a summary Index

| stats  count(eval(repayments_submit="1")) as repyaments_submit count(eval(forms_ChB="1")) as forms_ChB

The code works find, except that where the null value is null, it's shown as a zero and I'd like it to be blank.

I've tried count(eval(if(signout="1", ""))), but I receive the following error:

Error in 'stats' command: The eval
expression for dynamic field
'eval(if(signout="1", ""))' is
invalid. Error='The arguments to the
'if' function are invalid.'

Could someone look at this please and let me know where I've gone wrong?

Many thanks and kind regards

Chris

[IRHM73 · Accepted solution]

Hi, I found the solution which is:

sum(eval(if(signout="1", "1", "")))

Many thanks to all your suggestions and help.

Kind Regards

[somesoni2]

What's the current output (with original query) and what's the expected output?

[IRHM73]

Hi @somesoni2. Thank you for coming back to me with this.

The current output is a zero, but I'd like the value to be blank please.

Many thanks and kind regards

Chris

[cvssravan]

One correction in "if" function is already mentioned by renjith.

Regarding returning a blank value: When you use count, it will always return an integer, you may have to use another eval to set the field to blank if it is "0".

[IRHM73]

Hi @cvssravan.

Thank you for this. I'll have to look to see if this is possible.

Kind Regards

Chris

[renjith_nair]

@IRHM73,

if command expects three parameters - condition, "value when match", "value when no match"

so in your case , you need to add count(eval(if(signout="1", "","<value if signout doesnt match>")))

[IRHM73]

Hi @renjith.nair.

Thank you for coming back to me with this.

Unfortunately I'd like the field to be blank if it zero rather than having a value in it. When I have tried the code you kindly provided, even putting a text value in, the field still returns a zero.

Many thanks and kind regards

Chris

[noy72]

Would a "-" be acceptable?
"-","")))

[IRHM73]

Hi @noy72 . Thank you for coming back to me. This is something that I may have to think about. Although not ideal, it would at least have a greater difference visually from a number.

Many thanks and kind regards

Chris