Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

count in pivot table

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

1 Solution

Accepted Solutions
sunny_talwar

Try this:

=Sum(Aggr(If(

Sum(Numerator)

/

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

Capture.PNG

View solution in original post

10 Replies
sunny_talwar

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

Capture.PNG

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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?

sunny_talwar

So let me clarify this

Capture.PNG

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?

Anonymous
Not applicable
Author

Yes that is correct...

Anonymous
Not applicable
Author

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...

sunny_talwar

Try this:

=Sum(Aggr(If(

Sum(Numerator)

/

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

Capture.PNG

sunny_talwar

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