Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table of Month, year and Employee ID.
I am trying to create a chart that for each displayed year, show the count of employees for the last month exists in that year (if no selections are made - it should show Dec of all previous years, and for current year - it should show Mar data.
My dimension is: Year
My expression is:
=Aggr(count({<HC_MonthNum={$(=max(Aggr(max(HC_MonthNum),HC_Year)))}>}distinct EMP_ID),HC_Year)
I get the correct result for previous years and no data for current year:
HC_Year | #Emp |
2017 | 0 |
2016 | 200 |
2015 | 300 |
2014 | 400 |
the expected result is to show the number of employees in March for 2017 (this is the last month exists in the table for current year)
appriciate your assitance.
Thanks
Not exactly, I expect to see:
A, 2016, 2
A, 2017, 3
B, 2015,4
Others, 1
I expect to see the 2 divisions for all years when I limit Division dimension to 2.
Why does B, 2016, 2 is not shown?
because he has no data in Dec
Try this may be
Thanks for your help.
It works good for the test data, but when I try it on the real data, the QV document get stucked trying to calculate the expression.
Yes, this is going to be a resource intensive expression.. I have tried another approach, but don't think this is any better
I know, and this is not what I want. Is there any other way?