8 Replies Latest reply on Apr 10, 2018 3:29 PM by Peter Fakan

Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Below is the calculated field that I'm trying to use.

IF ISNULL(SUM(INT([Staff State]="ACD" OR [Staff State]="ONHOLD"))) THEN 0 ELSE SUM(INT([Staff State]="ACD" OR [Staff State]="ONHOLD")) END

• 1. Re: Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Hi,

Please post .twbx file or sample data.

• 2. Re: Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Hi,

Try using the ZN function which will help you to make nulls as 0.

Regards,

Pavan

• 3. Re: Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Attached is a simple scenario for the problem i'm facing

• 4. Re: Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Hi Ee,

Here's a cheap and nasty way of doing it - just change your Special Values to 0 by using the format > format fields menu

HTH

Peter

• 5. Re: Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Hi,

IF ISNULL(SUM(INT([Staff State]="ACD" OR [Staff State]="ONHOLD"))) = True

THEN 0

ELSE SUM(INT([Staff State]="ACD" OR [Staff State]="ONHOLD"))

END

Now it should work ok.

Trust this helps.

D

• 6. Re: Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Heyh Peter,

• 7. Re: Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Your file doesn't contain data. So, whatever calculations you do will not work.

~Tushar

• 8. Re: Trying to calculate the number of occurrences of a particular value from data source but becomes NULL when the particular field has no data. How to make it 0?

Hi Ee, here is a mockup using superstore data.

Note I modified the sum(Sales) to create some nulls: (but you won't have to do this)

To correctly select the measure, select the Fields drop down caret > the field you wish to edit > change the special values figure

HTH 10.5 attached

Peter