topic Re: set analysis to make sum of sales of last n periods (Year or Month). in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

Wed, 28 Dec 2016 11:35:35 GMT

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.

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

sunny_talwar — Wed, 28 Dec 2016 11:49:00 GMT

May be give this a try:

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

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

Wed, 28 Dec 2016 12:12:27 GMT

Hi Habib,

Thanks for wonderful wizard!

Parth

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

Wed, 28 Dec 2016 12:41:22 GMT

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

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

sunny_talwar — Wed, 28 Dec 2016 12:42:23 GMT

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)

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

Wed, 28 Dec 2016 12:52:43 GMT

Thanks a lot Sunny

It works!!

Best regards,

Parth

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

sunny_talwar — Wed, 28 Dec 2016 12:58:12 GMT

Super