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

ali_hijazi — Fri, 15 May 2020 02:37:55 GMT

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

when all columns are expanded I get the max month per month which is fine

when I collapse the Quarter column I get the latest month as per the data and this is fine

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

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

sunny_talwar — Fri, 15 May 2020 14:43:06 GMT

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

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

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

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