topic Re: Neglect NA In Pivot table in QlikView

Neglect NA In Pivot table

rupaliqlik — Thu, 04 Jan 2018 17:40:36 GMT

Dear Expert,

I have one tricky requirement where I have shown Zone and RTM wise different KPI performance which have Higer and lower Benchmark.If performance is greater than Higer then it's background colouris green ,if it is in between benchmark then amber colour and less than lower becnhmark then in red colour. So within this I have to count KPI but where data is null or NA then that data kpi should not count.Please provide some auggestions.

stalwar1‌ jagan‌ antoniotiman‌

Re: Neglect NA In Pivot table

sunny_talwar — Thu, 04 Jan 2018 18:02:47 GMT

Do you want to point at a specific example where this is happening?

Re: Neglect NA In Pivot table

rupaliqlik — Thu, 04 Jan 2018 18:05:14 GMT

Yes,where there written 'NA' in cell in pivot table.

Re: Neglect NA In Pivot table

sunny_talwar — Thu, 04 Jan 2018 18:10:31 GMT

I guess I understand that... but what are we trying to fix? Count, Color? Which expression?

Re: Neglect NA In Pivot table

rupaliqlik — Thu, 04 Jan 2018 18:14:50 GMT

If Kpi have data or it perform then it can count but it contain NA then

should not count as kpi .Means out of 3 if one of them is NA then total

parameters would be count 2 .And then we can say out of two higher

benchmark achieved 1 if its in green colour.please find attached excel in

community.

Re: Neglect NA In Pivot table

sunny_talwar — Thu, 04 Jan 2018 18:55:20 GMT

May be this

If(Len(Trim(count({<Category={"Greater than equal to 17"},FLAG={'Trainer_Manday'}>}distinct [Personnel Number])/count({<FLAG={'Trainer_Manday'}>}distinct [Personnel Number]))) > 0, 1, 0)+

If(Len(Trim(COUNT({<COMPLETION_STATUS={"Successful"},FLAG={'FLL LM 1'}>} distinct [Personnel Number])/COUNT({<FLAG={'FLL LM 1'}>}distinct [Personnel Number]))) > 0, 1, 0)+

If(Len(Trim(COUNT({<COMPLETION_STATUS={"Successful"},FLAG={'Aagman_Report'}>} DISTINCT [Personnel Number])/COUNT({<FLAG={'Aagman_Report'}>}DISTINCT [Personnel Number]))) > 0, 1, 0)