Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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.
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.
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.
Can you point me to a white paper discussion or some other place where I can learn more about this approach?
Thanks
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.
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.
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)
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.
But whatever. Do what you want.