Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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
Highlighted

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

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
Highlighted

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

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