Count for last month in each year

[karin_nevo]

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

[karin_nevo]

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.

[sunny_talwar]

Why does B, 2016, 2 is not shown?

[karin_nevo]

because he has no data in Dec

[sunny_talwar]

Try this may be

[karin_nevo]

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.

[sunny_talwar]

Yes, this is going to be a resource intensive expression.. I have tried another approach, but don't think this is any better

[karin_nevo]

I know, and this is not what I want. Is there any other way?