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

[#1]

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.

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

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

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

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

thanks a lot,

Parth

[#2]

Hi Patel,

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

Set Analysis Wizard for QlikView | qlikblog.at.

Br,

Habib Shaik.

[sunny_talwar]

May be give this a try:

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

[#4]

Hi Habib,

Thanks for wonderful wizard!

Parth

[#5]

Hi Sunny,

Thanks for your reply.

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 .

Truly appreciate your help.

Thanks and Regards,

Parth

[sunny_talwar · Accepted solution]

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]

Thanks a lot Sunny

It works!!

Best regards,

Parth

[sunny_talwar]

Super