Neglect NA In Pivot table

[rupaliqlik]

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‌

[sunny_talwar]

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

[rupaliqlik]

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

[sunny_talwar]

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

[rupaliqlik]

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.

[sunny_talwar]

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)