Count the number of TRUE value in a month.

[qlikviewforum]

We are using the below expression to find out whether the value is <1 or >1. If it is <1 it will mark as TRUE else it will mark as FALSE against each date and this we are doing in a straight table.

Now we have to do similar thing in a Pivot table and only difference is we will remove Period and add Month into cross tab and it should show number of TRUE value in a month. If there is a no TRUE value we can mark it as 0. Any idea how this can be achieved?

=IF(

  IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_30',$(e_Days('l_30')),

  IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_60',$(e_Days('l_60')),

  IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_90',$(e_Days('l_90')),

  IF(maxstring({<$(e_DateRange)>}Limit_Days)='TBD',$(e_Days('l_60')))

       )

   )

   )<1

  ,replace(sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 1),1,'TRUE'),

  replace(sum(TOTAL{<Period = {'>$(=date($(VMaxPeriod)-$(VDays))) <=$(=date($(VMaxPeriod))) '}>} 0),0,'FALSE')

   )

[qlikviewforum]

Some how I have managed to count number of TRUE value for a month using below expression and by adding Month in the dimension. But the issue here is when there is no TRUE value exists it has display 0 but in my case it is not displaying any records. Please help me out on this?

=Sum(Aggr(IF(

  IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_30',$(e_Days('l_30')),

  IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_60',$(e_Days('l_60')),

  IF(maxstring({<$(e_DateRange)>}Limit_Days)='last_90',$(e_Days('l_90')),

  IF(maxstring({<$(e_DateRange)>}Limit_Days)='TBD',$(e_Days('l_60')))

       )

   )

   )<1

  ,1, 0 ),Period))

[Anonymous]

have you tried to uncheck suppress null values in presentation tab for particular expression.

[qlikviewforum]

Yes already tried doing that but didn't work

[qlikviewforum]

Any one has any idea on this? When there is no TRUE(1) value in any particular month that particular month itself is not coming in the pivot table. So basically whenever there is no TRUE values I want to show 0 and when there is a TRUE value i want to show count of TRUE values.

If any one have any questions on this please let me know.

[Mark_Little]

Hi,

Make sure you are not suppressing nulls,

Then in the presentation tab at the bottom left change the Null Symbol to 0.

Not a hundred percent on what you are trying to achieve, but may look at a flag in the script and then you can count the flag. Also you would have 1 or 0 so no nulls.

Mark

[qlikviewforum]

Thanks for your response.

No we are not suppressing the nulls and also as you mentioned I have changes Null Symbol to 0(I don't think this has any impact since I am not seeing nulls against the month).