Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello - having an issue with FirstSortedValue and Aggr function. In the example below, I'm trying to identify the earliest month. I'm expecting that FirstSortedValue should be 201911 but formula is returning 201912. Any ideas? Thanks.
CASE_AER_NUMBER_VERSION | YearMonthNum | aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION) | FirstSortedValue( YearMonthNum, aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)) |
20151234567 (55) | 201911 | 201911 | - |
20151234567 (55) | 201912 | - | 201912 |
Thanks Sunny. I resolved it as follows with help from a different post of yours 😊:
YearMonth | CASE_AER_NUMBER_VERSION | sum(Aggr(if(YearMonthNum = Min({<YearMonth=>} TOTAL <CASE_AER_NUMBER_VERSION> YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum)) |
Nov-2019 | 20151234567 (55) | 1 |
Will you have YearMonthNum as a dimension in the chart? If not, then try this
FirstSortedValue(YearMonthNum, -YearMonthNum)
Thanks Sunny. I changed the dimension and updated the formula but still seeing incorrect results.
CASE_AER_NUMBER_VERSION | YearMonth | aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION) | FirstSortedValue(YearMonthNum, -YearMonthNum) |
20151234567 (55) | Dec-2019 | - | 201912 |
20151234567 (55) | Nov-2019 | 201911 | - |
Are you looking to see a single row where YearMonth = Nov-2019? or are you looking to see two rows, both with 201911?
Single row where YearMonth = Nov-2019
Thanks
The remove YearMonth as your dimension...
And use this as your expression
FirstSortedValue(YearMonthNum, YearMonthNum)
Thanks, though still not working.
CASE_AER_NUMBER_VERSION | aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION) | FirstSortedValue(YearMonthNum, YearMonthNum) |
20151234567 (55) | 201911 | - |
I am sorry, can you just try this Min(YearMonthNum)
Thanks Sunny. That did get the earliest month.
CASE_AER_NUMBER_VERSION | aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION) | min(YearMonthNum) |
20151234567 (55) | 201911 | 201911 |
But how do I now get a count of all of those cases that occur in the earliest month. In this case, I want to count Nov but not Dec. (That's why I was trying to use FirstSortedValue with Aggr)
CASE_AER_NUMBER_VERSION | YearMonthNum |
20151234567 (55) | 201911 |
20151234567 (55) | 201912 |