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

Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Showing results for 
Search instead for 
Did you mean: 
inmemoryofER
Contributor II
Contributor II

Calculating TOTAL by dimension

I have a table with two dimensions, store and year. I want to take a count of distinct employees at each store and calculate the percentage of employees at each store segmented by year. So in the table below, in 2018 store A had 6 of the total 17 distinct employees, which comes out to about 35%. I tried using the following expression: (Count(DISTINCT EMPLOYEES)/Count(TOTAL DISTINCT EMPLOYEES)), but the denominator is incorrect here because it returns a count of total distinct employees across all years.

Store Year Count(Distinct Employees) **Desired Calculation**
A 2018 6 6/17 = .35
B 2018 4 4/17 = .24
C 2018 7 7/17 = .41
A 2019 8 8/27 = .30
B 2019 7 7/27 = .26
C 2019 12 12 / 27 = .44
A 2020 5 5 / 26 = .19
B 2020 7 7 / 26 = .27
C 2020 14 14 / 26 = .54
Labels (3)
1 Reply
maxgro
MVP
MVP

 

try with 

Count(TOTAL <year> DISTINCT EMPLOYEES))