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
Hi karin,
try with the following example expression
=count({1<Monthfield= $::Monthfield, Yearfield = $::Yearfield>}employeefield)
Hi,
thanks, but this is not what I need.
I need to get the values in the last possible month per year.
meaning, if no selections are made: I need to get values for Decemeber for all years, expect for current year, which should show current month.
If I undestand the expression you sent me, it brings the possible values of the Month and year fields.
Can you share a sample .qvw
This?
If(GetSelectedCount(FieldName)=0, Sum({<Year -= {'$(=Max(Year))'}, Month = {'$(=Max(Month))'}>}Sales))
which should show current month.
This part i am not following. What was this
Sum({<Month = {'$(=Max(Month))'}>}Sales)
How about using FirstSortedValue() here
=FirstSortedValue(Aggr(Count(DISTINCT EMP_ID),HC_Year, HC_MonthNum), -Aggr(HC_MonthNum, HC_Year, HC_MonthNum))
I tried firstSortedValue, but when I limit the dimension to show only biggest 10 values (In also have another dimension of Department), it is not working (it shows 10 values but not the biggest).
Any idea why?
Would it be possible to share a sample? It is difficult to just read and understand what you might have....
HI,
I attached an example.
I added the original expression I used and the firstsortvalue you suggested.
the original expression I used doesn't provide data for 2017.
The firstsortvalue works until I use dimension limit. Then I use it in my original data (which I can't send) I don't get the top 10 values.
Here, I used top 2 values, and under others I don't see sum of all other divisions. I would like to see top 2 divisions, and for them - all years, and for all others - see total count.
Hope I eas clear...
Thank for your help
So, you are expecting to see something like this
Division, Count
A, 3
B, 4
Others, 1
Trying to understand what the final output needs to look like