Announcements
cancel
Showing results for
Did you mean:
Not applicable

## set analysis to make sum of sales of last n periods (Year or Month).

Hello all,

I need to have sum of amount for last n months in my set analysis equation.

I have following fields.

• Month like Jan,Feb,Mar
• Year like 2014,2015,2016
• [Year-Month] like [2014-12],[2015-01],[2015-02]
• Amount

If I select month "Mar" (2016) from Month field I should have sum of Amount of last 3 month (Dec 2015,Jan 2016 and Feb 2016).

I tried using set analysis in my equation but its not working.

I tried same on Year but didn't find any success.

By changing values of -3 and -1 I can manage periods but still need help.

thanks a lot,

Parth

1 Solution

Accepted Solutions
MVP

Ignore selections in Month and Year fields and let Year Month drive your selections (indirectly)

=Sum({<[Year - Month] = {"\$(='>=' & Date(AddMonths([Year - Month] ,-3),'YYYY-MM') & '<=' & Date(AddMonths([Year - Month] ,-1),'YYYY-MM'))"}, Month, Year>} Amount)

7 Replies
Not applicable
Author

Hi Patel,

Can you try this link once, might be it will useful to you.

Br,

Habib Shaik.

MVP

May be give this a try:

=Sum({<[Year - Month] = {"\$(='>=' & Date(AddMonths([Year - Month] ,-3),'YYYY-MM') & '<=' & Date(AddMonths([Year - Month] ,-1),'YYYY-MM'))"}>} Amount)

Not applicable
Author

Hi Habib,

Thanks for wonderful wizard!

Parth

Not applicable
Author

Hi Sunny,

I tried this expression earlier but it works only on [Year-Month] filter. As I make any selection on Month field, it makes Amount to 0.

And I also know why this is not working, because my field Month is in string and my set analysis make sum of Amount from (Current month - 3) to (Current month - 1) less than and greater than signs which are not applicable for strings.

All I need is to make expression working when I make any selection on Month field, which I am not able to do .

Thanks and Regards,

Parth

MVP

Ignore selections in Month and Year fields and let Year Month drive your selections (indirectly)

=Sum({<[Year - Month] = {"\$(='>=' & Date(AddMonths([Year - Month] ,-3),'YYYY-MM') & '<=' & Date(AddMonths([Year - Month] ,-1),'YYYY-MM'))"}, Month, Year>} Amount)

Not applicable
Author

Thanks a lot Sunny

It works!!

Best regards,

Parth

MVP

Super

Community Browser