topic Sum value for max date in App Development

Sum value for max date

geertdeman — Sat, 16 Nov 2024 07:03:58 GMT

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)

Re: Sum value for max date

sunny_talwar — Wed, 12 Dec 2018 13:15:13 GMT

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))

Re: Sum value for max date

geertdeman — Thu, 13 Dec 2018 10:24:01 GMT

Thanks!!! it is working

Re: Sum value for max date

geertdeman — Thu, 13 Dec 2018 10:40:20 GMT

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

Re: Sum value for max date

sunny_talwar — Thu, 13 Dec 2018 11:55:58 GMT

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

Re: Sum value for max date

geertdeman — Thu, 13 Dec 2018 13:07:16 GMT

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.

Re: Sum value for max date

sunny_talwar — Thu, 13 Dec 2018 13:11:06 GMT

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)

Re: Sum value for max date

geertdeman — Thu, 13 Dec 2018 13:56:12 GMT

also that is working!

Thanks.

Re: Sum value for max date

sunny_talwar — Thu, 13 Dec 2018 13:59:05 GMT

Awesome 🙂

Re: Sum value for max date

geertdeman — Fri, 14 Dec 2018 09:22:01 GMT

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

Re: Sum value for max date

sunny_talwar — Fri, 14 Dec 2018 12:01:06 GMT

Are you picking one of the two dimensions based on this variable value? $(vAux.Compliance.QPPVFlagValue)

Re: Sum value for max date

geertdeman — Mon, 17 Dec 2018 07:34:20 GMT

correct

Re: Sum value for max date

sunny_talwar — Mon, 17 Dec 2018 12:10:48 GMT

Then I believe dollar sign expansion should work

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

Re: Sum value for max date

geertdeman — Wed, 19 Dec 2018 09:33:07 GMT

it didn't work the first time, I tried to modify it and changed it back to your solution and it is working now!