topic Re: Sum for DateRange in App Development

Sum for DateRange

Luca1 — Fri, 15 Nov 2024 23:23:49 GMT

I want to sum up all Sales that are further in the past than 365 days. However, my current code returns 0. I suspect that there are some formatting issues with the max() function and the YearMonth format, but I'm not sure about that. Thank you for your help!

=sum(
{
<[OrderDate.autoCalendar.YearMonth]
= {'< max([OrderDate.autoCalendar.YearMonth]) - 365'}
>
}
Sales
)

Re: Sum for DateRange

Digvijay_Singh — Tue, 21 Dec 2021 00:44:00 GMT

Try this may be, assuming YearMonth is of type date.

=sum(
{
<[OrderDate.autoCalendar.YearMonth]
= {<$(=max([OrderDate.autoCalendar.YearMonth]) - 365)}
>
}
Sales
)

Re: Sum for DateRange

Luca1 — Tue, 21 Dec 2021 00:56:59 GMT

Hey, unfortunately this didn't help - I'm still getting 0 as a result even though that shouldn't be the case. But I appreciate your support!

Re: Sum for DateRange

Digvijay_Singh — Tue, 21 Dec 2021 01:03:42 GMT

Can you see at the bottom of the expression editor, normally I correct my formatting by looking at what I see over there...

Re: Sum for DateRange

Vegar — Tue, 21 Dec 2021 05:04:36 GMT

Could it be that you are making selections that are in conflict with your set expression?

What happens if you only make selections in the [OrderDate.autoCalendar.YearMonth] field?

You could also try to put double quotes around your modifier like I've done in my expression below.

=sum({< [OrderDate.autoCalendar.YearMonth] = {"< $(=addyears(max([OrderDate.autoCalendar.YearMonth]),-1))"}>}Sales)

Re: Sum for DateRange

Luca1 — Tue, 21 Dec 2021 08:38:30 GMT

Your suggestion doesn't work neither. Let me maybe elaborate on stuff that I've tried and behavior that I could observe:
1. If I use

=sum({<[OrderDate.autoCalendar.Year]={"2017"}>} Sales)

the output will be reasonable.

2. If try to achieve the same using

sum({<[OrderDate.autoCalendar.Year]={"=max(OrderDate.autoCalendar.Year)"}>)

the output is 0.

3. However, if I put

=max([OrderDate.autoCalendar.Year])

to a separate KPI cell, the output is 2017 (as expected).

Therefore, I think that you are right about that the set expression causes the error. I just don't understand what I do wrong.

Re: Sum for DateRange

Vegar — Tue, 21 Dec 2021 11:00:36 GMT

sum({<[OrderDate.autoCalendar.Year]={"=max(OrderDate.autoCalendar.Year)"}>)

You need to dollarexpand your expression in order to get the expression above to to work. Try changing it as I did below.

sum({<[OrderDate.autoCalendar.Year]={"$(=max(OrderDate.autoCalendar.Year))"}>)

Re: Sum for DateRange

Luca1 — Tue, 21 Dec 2021 11:12:37 GMT

Thank you so much, this was my mistake!