Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
karin_nevo
Contributor III
Contributor III

Count for last month in each year

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
20170
2016200
2015300
2014400

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

16 Replies
yoganantha321
Creator II
Creator II

Hi karin,

try with the following example expression

=count({1<Monthfield= $::Monthfield, Yearfield = $::Yearfield>}employeefield)

karin_nevo
Contributor III
Contributor III
Author

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.

Anonymous
Not applicable

Can you share a sample .qvw

Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

How about using FirstSortedValue() here

=FirstSortedValue(Aggr(Count(DISTINCT EMP_ID),HC_Year, HC_MonthNum), -Aggr(HC_MonthNum, HC_Year, HC_MonthNum))

karin_nevo
Contributor III
Contributor III
Author

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?

sunny_talwar

Would it be possible to share a sample? It is difficult to just read and understand what you might have....

karin_nevo
Contributor III
Contributor III
Author

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

sunny_talwar

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