12 Months Rolled Sum in Set Analysis

[#1]

Hi All,

I am struggling for getting a 12 month rolled sum for different years through a set analysis. I have actual sales figures for the past 3 years and similarly the Sales budget for the past 2 years.

I need to show the 12 monthly rolled actual sales for the year 2009 and 12 monthly rolled sales budget for the year 2010 in a pivot table where month is the dimension I am looking for. I tried different combinations of set analysis examples available in our forum but failed in getting the right result.

Attaching the sample QVW file for your reference.

Can someone spare some time and solve the issue?

Thanks in advance,

Sajeevan

[Oleg_Troyansky]

If I understand your definition of "12 month rolled sum" correctly, - you want to summarize Actual Sales for the previous 12 months for each one of the months in 2009, and similarily summarize 12 months of Budget for each month in 2010...

In other words, in the "March" row of your pivot table, you'd like to see Sales from April 2008 to March 2009, etc...

Well, Set Analysis alone can't help in your case. Set Analysis condition is being evaluated "globally", or in other words, outside of your chart's dimensions. So, if your dimension is a Month, the Set Analysis condition cannot take in account the individual Month values for each Month.

What I typically recommend doing is creating a second field for Month, often called "As of Month", and calculate all possible combinations of your original Month and "As Of Month". For each pair of Month - As Of Month, determine whether Month falls into a "rolling 12 months" period for the "As Of Month" and set a flag "Rolling 12 Flag" = 1 for all the combinations when Month is within 12 months from "As Of Month".

Then use "As Of Month" as your Chart Dimension, and use the Flag for your calculations:

Rolling 12 Sales = sum(Sales * R12_Flag)

Those transactions that belong to "rolling 12 Months", will get multiplied by 1 and therefore included, while the rest will be excluded.

[#3]

Hi Oleg,

Thanks for your quick reply.

Your understanding of my issue is correct. Do you have any example to share with me to understand how the combinations are made and flagged and them summed?

Best Regards,

Sajeevan

[#4]

Hi Sajeevan,

Try this in your expressions (one for Budget e one for Actual). Considering Period and Month as dimensions.

sum({<[Sub Item] = {Budget}>} Amount)

sum({<[Sub Item] = {Actual}>} Amount)

[Oleg_Troyansky]

I don't have any readily available examples at the moment, however this issue was described so many times in this forum, that you should be able to find an example ... If anyone else has an example of the "As of" logic - please post.

[#6]

Hi Sanjeevan

I am having same problem-Did you manage your way around it? If yes how-Can youp lease share.

THank and Regard

Nav

[#7]

Hi Snajeevan

I am struggling with same problem.did you manage your way around it-if yes can you please share.

Thanks

Nav

[johnw]

navsingh wrote:I am struggling with same problem.did you manage your way around it-if yes can you please share.

When I post examples of the approach discussed in this thread, I almost always call the table AsOf. So if you search for AsOf, you should get quite a few examples that I've posted:

http://community.qlik.com/Search/search.aspx?q=AsOf

[#9]

Hi Nav,

I loaded the data as below to a table called Data. The month variable is MM/DD/YYYY format.

Data:
load Month,Year(Month) as Period, Amount
FROM C:\Sales.xls (biff, embedded labels, table is Sheet1$);

I am setting the latest year which needs to be used in pivot table i.e. vMaxYear will be the current year

set vMaxYear = max(Period);

I created the rolling periods as below

RollingMonths:
load Month, Addmonths(Month,11) as RolledMonth, Year(Addmonths(Month,11)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,10) as RolledMonth, Year(Addmonths(Month,10)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,9) as RolledMonth, Year(Addmonths(Month,9)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,8) as RolledMonth, Year(Addmonths(Month,8)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,7) as RolledMonth, Year(Addmonths(Month,7)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,6) as RolledMonth, Year(Addmonths(Month,6)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,5) as RolledMonth, Year(Addmonths(Month,5)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,4) as RolledMonth, Year(Addmonths(Month,4)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,3) as RolledMonth, Year(Addmonths(Month,3)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,2) as RolledMonth, Year(Addmonths(Month,2)) as RolledPeriod
resident Data;
load Month, Addmonths(Month,1) as RolledMonth, Year(Addmonths(Month,1)) as RolledPeriod
resident Data;
load Month, Month as RolledMonth, Year(Month) as RolledPeriod
resident Data;

I added a pivot table and set the dimensions as below

=Month(RolledMonth)

I set the expressions as below for the previous year (i.e. vMaxYear-1)

sum({<RolledPeriod={$(=vMaxYear-1)}>} Amount)

I set the expressions as below for the current year (i.e. vMaxYear)

sum({<RolledPeriod={$(=vMaxYear)}>} Amount)

Hope this will solve your issues. I usually follow what John and Oleg suggests.

Best Regards,

Sajeevan

[#10]

Hi Sanjeevan/John/Oleg

Thanks a lot for the help.Will get back if have any issues.

Kind Regards

Nav