'if' statement works in straight table but not Text object

[#1]

Hi,

My data contains Accounts, Tasks and Delivered Status.  The status can be "delivered" (1), "not delivered" (-1) or "not actioned" (0).  I want a count of all the accounts that ONLY have "not actioned" or blank.  In other words i'm trying to find accounts that have zero activity.  My thought was to count delivered and not delivered - if this was zero, then count that account.

My expression is as follows:

=if (count ({<Delivered -= '0')>} distinct Accounts) = 0, count (Accounts), 0)

This works in a straight table and it lists the accounts.  However, I want it represented as a single number via Text Box which returns a 0 (zero).

Should i be using another expression?

Thanks for any help.

Cheers.

[tresesco]

May be simply  = Count ({<Delivered-={1,-1}>} Distinct Accounts) ?

[MayilVahanan · Accepted solution]

Hi

Try like this

= Sum(Aggr(if (count ({<Delivered -= '0')>} distinct Accounts) = 0, count (Accounts), 0), Dim1))

Note:

Dim1 is the dimension used in the straight table.

[#4]

Hi,

You can use simply like

Count ({<Delivered-={1,-1}>} Distinct Accounts)

Regards

Deepak

[jonathandienst]

Hi

Is the 'blank' Delivered Status a null? If it is an empty string, then tresesco's  expression should meet your requirements.

If it is null, then you have a problem. Set expressions work the same as selections from the front end, and you cannot select null values. In the same way, you cannot select null values using a set expression. So {<Delivered-={1,-1}>} will return the zeroes, but not the nulls.

You can use Sum(If()), like this:

Sum(Distinct If(Delivered = 0 Or IsNull(Delivered), Accounts, 0))

This will select nulls, but Sum(If()) is a performance killer in larger data sets. So if you cannot use Sum(If()), then you have two options:

• Apply NullAsValue to the field. Then {<Delivered-={1,-1}>} will select the nulls (now blanks) and 0's

          Before the LOAD, add the line:

               NULLASVALUE Delivered;

• Set a flag to indicate the null value, and use this flag in the expression. Something like:

          LOAD ....

               Delivered,

               If(IsNull(Delivered) Or Delivered = 0, 1, 0)  As DeliveredNoAction,

               ...

          Now your expression is Sum({<DeliveredNoAction={1}>} Distinct Accounts)

HTH

Jonathan

[sasikanth]

HI

Try like this

=if (count ({<Delivered ={*}- {0})>} distinct Accounts) = 0, count (Accounts), null())

[#7]

Hi,

Thanks! This worked.

Cheers.