Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Help in expression (put computed column value in set analysis)

Hello got a pivot table with Year, Quarter, and month as dimensions

I managed using the FirstSortedValue function to get the latest month per month, or per quarter or per year
Now what I want is to use this value in my expression to get the headcount 

expand_all.png 
when all columns are expanded I get the max month per month which is fine
expand_all2.png
when I collapse the Quarter column I get the latest month as per the data and this is fine
expand_all3.png
and when I collapse the year column I get the latest month in the year and this is fine
Now what I want is to count(distinct Employee) where monthYear is the one I'm getting using the FirstSortedValue
so when the year is collapsed I should get 1 since in 201905 the distinct count of employee is 1
check attached sample data and help please @rwunderlich , @sunny_talwar 

I can walk on water when it freezes
1 Solution

Accepted Solutions

Try this

Pick(Dimensionality(),
	Sum(Aggr(If(YEARMONTH = Max(TOTAL <YEAR> YEARMONTH), Count(DISTINCT EMPLOYEE)), YEAR, Quarter, YEARMONTH)),
	Sum(Aggr(If(YEARMONTH = Max(TOTAL <YEAR, Quarter> YEARMONTH), Count(DISTINCT EMPLOYEE)), YEAR, Quarter, YEARMONTH)),
	Count(DISTINCT EMPLOYEE)
)

View solution in original post

1 Reply

Try this

Pick(Dimensionality(),
	Sum(Aggr(If(YEARMONTH = Max(TOTAL <YEAR> YEARMONTH), Count(DISTINCT EMPLOYEE)), YEAR, Quarter, YEARMONTH)),
	Sum(Aggr(If(YEARMONTH = Max(TOTAL <YEAR, Quarter> YEARMONTH), Count(DISTINCT EMPLOYEE)), YEAR, Quarter, YEARMONTH)),
	Count(DISTINCT EMPLOYEE)
)

View solution in original post