topic count in pivot table in QlikView

count in pivot table

Anonymous — Fri, 24 Feb 2017 11:27:03 GMT

Hi,

I have a pivot table whre i need to count the KPI's basd on target

I dont want target as a dimension in a chart how ever i need the count value based on target column

if KPI sum ie NUm/denom is >= target then i need to count the target values.

Could anyone please help me on this?

I dont want target as a dimension in a chart how ever i need the count value based on target column

Re: count in pivot table

sunny_talwar — Fri, 24 Feb 2017 12:00:20 GMT

Where are you getting 2.98 from? Can you point out in the below screenshot?

Re: count in pivot table

Anonymous — Fri, 24 Feb 2017 12:33:18 GMT

Hi sunny sorry it is 2.948 thht is for childcare less than Target , 100.3 for control > target ..... for those respective areas

i need this count

Re: count in pivot table

sunny_talwar — Fri, 24 Feb 2017 12:49:37 GMT

So, in terms of a numerical output, what is your desired output here?

Re: count in pivot table

Anonymous — Fri, 24 Feb 2017 12:52:05 GMT

i am not sure of accurate amount.... may be 5 or 7

i need to figure out that.. but my intention is to get the numerics..

do you find any way?

Re: count in pivot table

sunny_talwar — Fri, 24 Feb 2017 12:55:58 GMT

So let me clarify this

Here we have all the child care numbers less than Target so we will count 3 for Area1?

2.95 < 90

50.35 < 91

0.99 < 95

Area1 Count of Child Care = 3?

Re: count in pivot table

Anonymous — Fri, 24 Feb 2017 13:00:01 GMT

Yes that is correct...

Re: count in pivot table

Anonymous — Fri, 24 Feb 2017 13:01:32 GMT

however we dnt need that Target in dimensions but for understanding i have created this.. this target value comparison has to be make in expression...

Re: count in pivot table

sunny_talwar — Fri, 24 Feb 2017 13:02:39 GMT

Try this:

=Sum(Aggr(If(

Sum(Numerator)

Sum(Denominator) <= Target, 1, 0), AreaID, Target, KPI_Category))

Re: count in pivot table

sunny_talwar — Fri, 24 Feb 2017 13:03:22 GMT

Yup, as you see above, Target is not one of the dimensions

Re: count in pivot table

Anonymous — Fri, 24 Feb 2017 13:18:10 GMT

thanks will look into it