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

Rolling 12 Months

I know there have been several posts on the rolling 12 months question. I have reviewed them all and tried to apply the principle to my solution but I have not been successful. I have the following graph:

error loading image

the numbers above the Fiscal YearQtr Dimension should be the sum of the Days for the records in the table that fall within the previous 365 days. However as you can see it is just the sum of the Days within the Fiscal YearQtr displayed. My expression to obtain this number is:

sum({$<ACTUAL_INDUSTRIALIZE_CPLT_DT={">=$(today()-365)"},PROJECT_COMPLEXITY_CDE={2}
, [Fiscal Year]=, [Fiscal YearQuarter]=, [Fiscal YearMonth]=>}
(ACTUAL_INDUSTRIALIZE_CPLT_DT-ACTUAL_DEFINITION_CPLT_DT)
)

Can someone please help give me the formula to give me the correct results?

Thank you in advance.

Guy Tanguay

7 Replies
pover
Luminary Alumni
Luminary Alumni

You might need to use a "Date Island" table or a "AsOfTable" to do the rolling 12 month chart. Look for these subjects in the forum and if you are still having trouble, explain a little more what you are looking for.

Regards.

johnw
Champion III
Champion III

I agree with Karl's suggestions.

There's "no" way to use set analysis to do it, because a set is only evaluated once for the whole chart, not once per row. In other words, you can't have the set recognize that the current data point is for 2011Q1, and therefore go 365 days back from that. There is a complicated way around that by generating different set analysis expressions for every single data point, but it's really not the right solution to the problem in my opinion. A date island table is a simple solution if your data set is small. An AsOf table is a more complicated solution, but should have much better performance on large data sets.

As he says, let us know if you need more of an explanation after searching for those.

Hmmm, well, this isn't your specific need, but the attached file demonstrates both techniques and a couple variations of the AsOf table technique.

Hmmm, also, it puts the date fields on your main table. In practice, you'd want a separate calendar table, and then load the date data from your calendar. I should probably clean up the example to do that, but I'm lazy.

Not applicable
Author

Hi John,

If I select any year then the revenue for the first and second month of the selected year should use the revenue from the previous year.

Ex: Revenue of each month

Month Revenue
Nov2008 200
Dec2008 300
Jan2009 400
Feb2009 500
Mar2009 100

When I select the Year=2009, Rolling 3 months revenue as per your solution is

Month Revenue
Jan2009 400
Feb2009 900
Mar2009 1000

But it should consider the previous year's revenue to calculate the rolling months revenue i.e Rolling 3 months revenue of Jan2009 is sum of Nov2008,Dec2008 and Jan2009.

Month Revenue
Jan2009 900
Feb2009 1200
Mar2009 1000

How to implement this in expression?

To achieve this I have used "For loop" in script part. Since my table has around 10 million records, this for loop is not taking much time.

for i=$(V_Min_Month) to $(V_Max_Month) //Month format is 'YYYYMM'

if right($(i),2)>00 and right($(i),2)<13 then //to allow the valid months(from YYYY01 to YYYY12)

// In this example I have not conside the current month i.e Jan 2009's Revenue=sum of Dec2008,Nov2008 and Oct2008's revenue
Let V_Month_To=if(match(right($(i),2),01,02,03),$(i)-91,$(i)-3); //to calculte the previous 3rd month.

Fact:

LOAD
$(i) As Year_Month,
Cntry_Cd,
Prod_Cd,
Sum(Revenue) as Revenue

FROM
QVD\Revenue .qvd
(qvd)
Where Year_Month<$(i)
and Year_Month>=$(V_Month_To)
Group by Cntry_Cd,Prod_Cd;

End if

Next i;

Please share your ideas on this.

Not applicable
Author

Can you point me to a white paper discussion or some other place where I can learn more about this approach?

Thanks

johnw
Champion III
Champion III


shans_bala wrote:If I select any year then the revenue for the first and second month of the selected year should use the revenue from the previous year.


Right.


shans_bala wrote: Nov2008 200
Dec2008 300
Jan2009 400
Feb2009 500
Mar2009 100

When I select the Year=2009, Rolling 3 months revenue as per your solution is
Month Revenue
Jan2009 400
Feb2009 900
Mar2009 1000


No it isn't. Perhaps you missed it because the example calculates a three month rolling AVERAGE instead of sum, and that average for Jan 2009 is very close to the data value for Jan 2009? Start with the data table on the left.

2008 Nov 984
2008 Dec 278
2009 Jan 594

The three month rolling average for January should be (984 + 278 + 594)/3 = 618.66667, and that's indeed what displays.

As for my ideas on the script solution, I don't think I have ever resorted to doing aggregation in a script in a real application, and I would only do so as a last possible resort if there was no other way to get the required performance. Any script subtotals will not be sensitive to selections, like if we wanted to select a single customer, and only see that customer's rolling three month revenue. Similary, you're locked into a specific way of aggregating, and can't just make a chart that instead aggregates by customer, say, without changing your data model. Aggregating data on the fly is a QlikView strength, and I feel we should take advantage of that by keeping all of our data at the lowest practical level, and only aggregating in the objects on the screen.



Not applicable
Author


John Witherspoon wrote:
No it isn't. Perhaps you missed it because the example calculates a three month rolling AVERAGE instead of sum, and that average for Jan 2009 is very close to the data value for Jan 2009? Start with the data table on the left.
2008 Nov 984
2008 Dec 278
2009 Jan 594
The three month rolling average for January should be (984 + 278 + 594)/3 = 618.66667, and that's indeed what displays.


But I didn't see the rolling 3 months average for 2009 Jan when I select the year=2009.

Please see the screenshot (taken from your example)

While calculating the rolling months in script part, we can find the rolling 3 months revenue for a particular customer and it is similar to other fact tables. Please see the attached qvw file.

johnw
Champion III
Champion III


shans_bala wrote:But I didn't see the rolling 3 months average for 2009 Jan when I select the year=2009.
Please see the screenshot (taken from your example)

Ah, OK, so you were getting confused because I packed multiple ways of handling the same thing into the same application for comparison purposes, and thus have five different Year fields, each associated with either the raw data or one or more of the charts, and most of them interconnected. You wouldn't put ALL of these fields into an application, and you might not even make the "Year" field selectable.

What you've selected is the "Year" field. You can see and select that because I chose to put the raw data on the screen so that you can see it and verify the calculations if desired. Each chart, though, is driven by its own specific year field.

So in the top chart, driven by ChartYear, you should be selecting a ChartYear. If you select a ChartYear of 2009, it will give you the right answer for Jan 2009.

In the second chart, driven by AsOfYear, you should be selecting an AsOfYear. If you select an AsOfYear of 2009, it will give you the right answer for Jan 2009.

In the third chart, driven by RollingThreeMonthsAsOfYear, you should be selecting a RollingThreeMonthsAsOfYear. If you select a RollingThreeMonthsAsOfYear of 2009, it will give you the right answer for Jan 2009.

My suggested approach is chart 2. Date islands suffer from performance problems on larger data sets. And an AsOf table specific to each desired period (rolling three months and YTD in the example) may create user confusion as they select fields, just like you've encountered. A single, generic AsOf table DOES have its own weaknesses of course. If you put both Year and AsOfYear in the application as selections, then you invite the same sort of confusion from users that you just encountered yourself. If you put only AsOfYear in as a selection, then you will likely have to support something like a "Selected Time Period Only?" flag in the AsOf table, and refer to this flag using set analysis in your charts that simply want to show the exact time period the user selected.


shans_bala wrote:While calculating the rolling months in script part, we can find the rolling 3 months revenue for a particular customer and it is similar to other fact tables. Please see the attached qvw file.

Yes, HOW to aggregate in script isn't a mystery to me. My point was that if you started with a summary table in your script that aggregates by month, you "can't just make a chart that... aggregates by customer... without changing your data model." Obviously you CAN change your data model to aggregate by customer. And then if you need to aggregate by product, you can create a second summary table or add product to your existing table. And if you need to aggregate by color and size, you can create a third summary table or add color and size to your existing table. And if you need to aggregate by city and state, you can create a fourth summary table or add city and state to your existing table. OR you can just keep your data at the lowest level and let the product do the aggregation for you. That's what it's there for.

But whatever. Do what you want.