Capture Null Value in Pie Chart Calculated Dimension and set to text 'Unspecified'

[#1]

Hi all,

Any help is greatly appreciated; I have a Pie Chart with a calculated dimension of below; I am trying to set if the resulting value is Null to text 'Unspecified'.

Logic: If no 'Branch' field selection is applied then calculate if 'Receiving Officer Branch' is equal to Customer Relations or not. If a single 'Branch' field is selected then calculate if 'Receiving Officer Branch' is equal to the 'Branch' selection else if 'Receiving Officer Branch' is equal to Customer Relations.

[Pie Chart Expression is simply a count of service requests]

Calculated Dimension:

If( GetSelectedCount(Branch) < 1 OR GetSelectedCount(Branch) > 1,

     If([Receiving Officer Branch] = 'Customer Relations', 'Customer Relations', 'Not Customer Relations') ,

          If( [Receiving Officer Branch] = GetFieldSelections(Branch), GetFieldSelections(Branch),

               If( [Receiving Officer Branch] = 'Customer Relations', 'Customer Relations', 'Not Customer Relations')))

Some of the data is inconsistent and results in no Branch or Receiving Officer Branch existing for the record so shows as null ('-').

I have tried many ways of trying to pick up the null value but none have worked. Why cant I just use any of the following in a If statement around the whole expression like below? If I do use either of these examples the null still shows.

If( IsNull(Calc.Dimension) , 'Unspecified', Calc.Dimension)

or

If( Len(Trim(Calc.Dimension)) < 1 , 'Unspecified', Calc.Dimension)

- IsNull()

- Len() < 1

- Len(Trim()) < 1

Any suggestions would great 🙂

Thanks, Joe

[shadoworth]

I would say that your "-" value in not due to the calculation dimension but to the fact that for some rows qlik cannot find a value for [Receiving Officer Branch] and hence all the if condition cannot be evaluated.

Try:

If( GetSelectedCount(Branch) < 1 OR GetSelectedCount(Branch) > 1,

     If(isnull([Receiving Officer Branch]),'Unspecified',

          If([Receiving Officer Branch] = 'Customer Relations', 'Customer Relations', 'Not Customer Relations') ,

               If( [Receiving Officer Branch] = GetFieldSelections(Branch), GetFieldSelections(Branch),

                    If( [Receiving Officer Branch] = 'Customer Relations', 'Customer Relations', 'Not Customer Relations'))))

Moreover, regardless to you problem, I suggest you to brake your calculated dimension into 2 and to use the "Enable Condition" to set your condition on the selections made by the user on the field [Branch]