Skip to main content
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with Set Analysis

Good morning everyone!

I need some help with an expression a little unusual.

On my dashboard, I always have an expression of the actual month and beside it, the previous month.

OK, for now, it's working.

But I have an actual month expression that I have to show the previous month, and the previous month is the two months ago. So, the first thing I have done was change the actual month expression with the previous month expression and the previous I've subtract two months; and again, worked.

However, there is a situation that this is not working, when I have to select several months to see the accumulated value, it is returning only the last value (the previous month or month - 1, for the actual month).

Here is the expression that I have been using for this calculate:

=sum({< %ID= {$1} , YearMonth={$(=date(addmonths(max(%DtMov),-1),'YYYYMM'))}, Year=, Month= >} Total_Expression)

Sorry my poor English.


2 Replies

If you select several months then max(%DtMov)will return the latest, i.e. the max, month. Set analysis calculates one set per chart, not per row. So if you use YearMonth as a dimension you won't get the results you expect if you select several months. See this document for more information: Calculating rolling n-period totals, averages or other aggregations

talk is cheap, supply exceeds demand
Not applicable

Thanks Gysbert, for several months it's working, but it's not getting the previous month for the first month of the filter, and of course, for only one month, it's getting the currently month either, not the previous.

I think I'm near to a closure.

Look this numbers example:

Result    Month

2            01

3            02

5            03

0            04

But the result must be:

Result    Month

0            01 (Result from December)

2            02

3            03

5            04

Here is the expression now:

=sum({<YearMonth={'>=$(=min(YearMonth))<=$(=max(YearMonth)-1)'}>} Total_Expression)

Do you have any ideas for me???