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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
JC_1
Contributor
Contributor

Calculating count of aggregate averages that meet a criteria

Hi,

Apologies if this has been answered before as I am new to QlikSense. I am stuck with calculating a KPI that my company requires. 

Basically, I would like to find out the % of subjects taught across all institutions, taught on Campus that have a scoring of > 3.2 in a KPI chart.

So far, I am able to get the total number of distinct counts of subjects taught on campus across all institutions based on the following expression:

COUNT({<[Acad Year] = {"2019"}, [Location] = {"Campus"}>}Aggr(Aggr(Avg([Scoring]),[Subjects]),[Subjects]))

How do I calculate the distinct count of subjects meeting the above parameters AND have a scoring of > 3.2? That way I can use this to divide by the above expression to get my desired KPI value. 

Sample data set:

Acad YearInstitutionSubjectsLocationScoring
2019AMathCampus3
2019ABiologyHBL3.2
2019AChemistryCampus3.4
2019APhysicsCampus3.5
2019BMathHBL3.1
2019BBiologyCampus3.2
2019BChemistryCampus3.5
2019BPhysicsHBL3.7
2019CMathHBL3.3
2019CBiologyCampus3.6
2019CChemistryHBL3.8
2019CPhysicsCampus2.8

 

Taking into consideration only those taught on Campus:

Avg Math scoring = 3, Avg Chemistry scoring = 3.45, Avg Physics Scoring = 3.15, Avg Biology Scoring = 3.4

The result of % of subjects that score less than 3.2 would be 50% (result that I would like to get). 

 

Appreciate any help I can get! 

Labels (1)
0 Replies