Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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.

=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

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

7 Replies
Not applicable
Author

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)

Not applicable
Author

Hi Habib,

Thanks for wonderful wizard!

Parth

Not applicable
Author

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

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

sunny_talwar

Super