topic Re: Set analysis - Comparison with variable in App Development

Set analysis - Comparison with variable

Shoro — Mon, 09 Nov 2020 12:42:25 GMT

Hi,
I want to get the last month's Sum of sales.

I use a variable called vLastMonth:
= Text (Year (Today ())) & '-' & Month (AddMonths (Today (), - 1))

And this is the formula to get the Sum of last month's sales:

Sum ({$ <[Order Date.autoCalendar.YearMonth] = {"vLastMonth"}>} Sales)

But the result is: 0.
Why?

If I put "2020-Oct" it works.

Why does working with the variable fail?

I would also love to understand, why if I add $ to a variable
Sum ({$ <[Order Date.autoCalendar.YearMonth] = $ {"vLastMonth"}>} Sales)

I get the Sum of all sales without filtering of months (last month).

Thanks for your help

Re: Set analysis - Comparison with variable

Taoufiq_Zarra — Mon, 09 Nov 2020 12:45:20 GMT

@Shoro you can try this :

=Sum ({$ <[Order Date.autoCalendar.YearMonth] = { '$(vLastMonth)' }>} Sales)

and check date format for Order Date.autoCalendar.YearMonth must be YYYY-MMM

and example :

Re: Set analysis - Comparison with variable

Shoro — Mon, 09 Nov 2020 13:03:52 GMT

I found that the correct formula is:
Sum ({$ <[Order Date.autoCalendar.MonthsAgo] = {1}>} [Sales])

But I would still love to understand why the previous formula does not work.

Re: Set analysis - Comparison with variable

Gabriel — Mon, 09 Nov 2020 14:02:52 GMT

@Shoro

I think the reason why this Sum ({$ <[Order Date.autoCalendar.YearMonth] = $ {"vLastMonth"}>} Sales) didn't work is because you put the double quote and the dollar expansion ($) in the wrong place. See below

Sum ({$ <[Order Date.autoCalendar.YearMonth] = {"$(vLastMonth)"}>} Sales)

Hopefully this helps