Sum value for max date

[geertdeman]

I need the total  value for each company for the maximum month (and also for maximum month -1).  My calculation is working as long there is data for all months but once there is a month missing (in the example november is missing for company A)  the calculation of that value is wrong.  This is the calculation I used for Sum Cur Month: sum({<YearMonth={"$(=Date(max(Date#(YearMonth, 'MMM-YYYY')),'MMM-YYYY'))"}>}
Counter). 

The calculation that I used for column current month is working like expected: Date(max(Date#(YearMonth, 'MMM-YYYY')),'MMM-YYYY')

Why is that sum function not using the maximum month value for that company as filter, it seems like it is using november 2018 for all companies

(Photo shows a simplified version of the data, it is also possible to filter on company, period and a couple of other dimensions.  Data should be updated with the selected filters)

[sunny_talwar · Accepted solution]

Set analysis is evaluated once per chart and not once per dimension... in your case, set analysis is evaluated to be Nov-2018 for the whole chart... so unfortunately, you cannot use set analysis in this case, unless you create a max month flag in the script and use that in your set analysis where for company A, Oct is flagged as 1 and for Company B and C, Nov is flagged as 1.

Alternative to this would be to use something like this

FirstSortedValue(Aggr(Sum(Counter), YearMonth, Company), -Aggr(Date#(YearMonth, 'MMM-YYYY'), YearMonth, Company))

[geertdeman]

Thanks!!! it is working

[geertdeman]

that is working for the current month, but can I use that also for the previous month?  In the output I show the value for the current month and in another column the value of the previous month

[sunny_talwar]

Would you be able to elaborate on what you would want to see as the output based on your sample data?

[geertdeman]

It is the data for the previous month, it is visible in the example, the column Sum prev.  For company B and C it is the value for October, for A it is the data for September.  Additional question, what if there are some default filters that needs be applied on other fields, not visible in the app.

[sunny_talwar]

You only have Company as the dimension of your chart? If that is correct, try this

FirstSortedValue(
  Aggr(
    Sum(Counter)
  , YearMonth, Company),
  -Aggr(
    Date#(YearMonth, 'MMM-YYYY')
  , YearMonth, Company),
2)

[geertdeman]

also that is working!   

Thanks. 

[sunny_talwar]

Awesome 🙂

[geertdeman]

Another issue that I have, that first column company is a calculation: 

Pick($(vAux.Compliance.QPPVFlagValue),
[REC_COMP_CATEGORY_CHANGED],
[REC_COMP_REGION_GROUPING],
)

But that doesn't seems to work as field value in the firstsortedvalue function