I realize numerous examples are present of this Rolling 12 Month average are already present on the community but none of them 'intergrate' with a calander(s) already defined. At least I can not find any.
Our fiscal year runs Nov to Oct. I would like the end user to select YearFiscal and the Rolling Margin chart to display the last rolling 12 month margin. The total GP for the last 12 months is just fine as well. I can then divide by 12 to find monthly average. I was able to easily do this (Rolling Margin equation) within the current fiscal year but going back in this case 5 months proved to be an issue. As the other forumns said Set Analysis is not the answer and I was unable to get 'As Of' tables to work properly. They all seemed to define thier own time parameters. My goal would be for it look similar to the Rolling 12 Month Total - GP column in the manually caculated Excel image 'Goal'. Currently it looks like 'Current' screenshot.
Does any one of any examples of how to do this or other ideas?
Okay I was able to perform this function however the solution is not ideal. I converted each expression into a variable. This left me with the corect answer but without a demension to graph on since this all performed in an expression.
Jose thanks for the response. I have performed a situation like this already. When I click on '2012' I only go back in the current fisical year. I would like to go back 12 months. Can this be done? What equations would you use in a line chart to graph this?
Ironic. This is why I started this thread. I had a tough time finding a solution with intergration into an already existing calander. My single expression solution is above as well. It is not ideal and graphing is highly limited as I am sure you discovered as well. I had a lot of views on the post but you were the first reply back with a 'working' solution.
Thanks for all your help. Perhaps someone else has a working solution.
The solution is not in the calendar : you have to create a pivot table between your calendar and the fact table.
In this pivot table you have 2 type : DIRECT and CROSSING.
For type DIRECT, a row in fact table is linked to the date in calendar
For type CROSSING, a row in fact table is linked to all the dates of the 12 future months in calendar.
So in Qlikview, you use the type DIRECT all the time, except when you want to present for each month the total of the past 12 months. In this case you use CROSSING because all rows are linked to the dates of the 12 future months, so it means (reversed point of view) that a month is linked to all data of the past 12 months.
Thanks a lot nstefaniuk. This is exactly what I as looking for. I followed example 2 in the QVW. I now have a loosely coupled data because I placed the 'Period_id2/Final' in my fiscal calander as well my normal calender and these two tables were already linked together. Not sure how to resolve this at the current time but the data is correct. (We have a spreadsheet down by hand to match to.) If I figure this out I may post my example. Thanks.