Qlikview Rolling 12 Month Fiscal

Multi tool use
This forum seems to be more geared toward more programming issues but I would be interested if someone would look at the logic issue in the below link. My issue is how to calculate the previous 12 month total using the calendar already defined. I have found very few examples of this on the QV community. Please see the below link for more details. I would be willing to look at a SQL or a QV script solution.
Our fiscal year runs Nov to Oct. I would like the end user to select Year and a chart to display the last rolling 12 month margin. I have had issues getting my total to accumulate for previous months. My goal would be for it look similar to the Rolling 12 Month Total - GP column in the manually caculated Excel image 'Goal'. (look at QV link for screenshot)
Rolling Margin equation: my attempt to use Set Analysis to make a rolling avg equation
=Sum({<master_date={'>=$(=MonthStart(Max(master_date), -12))<=$(=MonthEnd(Max(master_date)))'}>}
MasterCalendar
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
MasterCalendar: this uses master_date to connect items together. This an fiscal calendar are hard to put together with rolling avg
LOAD
TempDate AS master_date,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
If(Num(TempDate) >= $(vYearStart) AND Num(TempDate) < $(vMonthNow), -1, 0) AS YTD,
If(Num(TempDate) >= $(vYearStartLY) AND Num(TempDate) < $(vMonthNowLY), -1, 0) AS LY_YTD,
Year2Date(TempDate) * -1 AS YTD_Flag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LY_YTD_Flag
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
FiscalCalander> This defines our fiscal year
FiscalCalendar:
LOAD date(date#(20011101,'YYYYMMDD')+recno(),'MM/DD/YY') AS "master_date"
AUTOGENERATE today()-date#(20011101,'YYYYMMDD');
LEFT JOIN (FiscalCalendar)
LOAD
"master_date",
date(monthstart(master_date),'MMM YY') AS "MonthFisical",
date(monthstart(master_date),'MMM') AS "MonthFisical_MonthTitle",
date(yearstart(master_date,1,11),'YYYY') AS "YearFiscal",
month(master_date)-month(num(today(1))) AS FiscalMonthsElapsed,
YearToDate(master_date, 0,11)*-1 AS YTD_FLAG_Fiscal,
YearToDate(master_date,-1,11)*-1 AS LY_YTD_FLAG_Fiscal
RESIDENT FiscalCalendar;
To see screen shots:
http://community.qlikview.com/message/219912#219912
Thank you for taking the time to look at this issue.
business-intelligence qlikview
add a comment |
This forum seems to be more geared toward more programming issues but I would be interested if someone would look at the logic issue in the below link. My issue is how to calculate the previous 12 month total using the calendar already defined. I have found very few examples of this on the QV community. Please see the below link for more details. I would be willing to look at a SQL or a QV script solution.
Our fiscal year runs Nov to Oct. I would like the end user to select Year and a chart to display the last rolling 12 month margin. I have had issues getting my total to accumulate for previous months. My goal would be for it look similar to the Rolling 12 Month Total - GP column in the manually caculated Excel image 'Goal'. (look at QV link for screenshot)
Rolling Margin equation: my attempt to use Set Analysis to make a rolling avg equation
=Sum({<master_date={'>=$(=MonthStart(Max(master_date), -12))<=$(=MonthEnd(Max(master_date)))'}>}
MasterCalendar
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
MasterCalendar: this uses master_date to connect items together. This an fiscal calendar are hard to put together with rolling avg
LOAD
TempDate AS master_date,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
If(Num(TempDate) >= $(vYearStart) AND Num(TempDate) < $(vMonthNow), -1, 0) AS YTD,
If(Num(TempDate) >= $(vYearStartLY) AND Num(TempDate) < $(vMonthNowLY), -1, 0) AS LY_YTD,
Year2Date(TempDate) * -1 AS YTD_Flag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LY_YTD_Flag
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
FiscalCalander> This defines our fiscal year
FiscalCalendar:
LOAD date(date#(20011101,'YYYYMMDD')+recno(),'MM/DD/YY') AS "master_date"
AUTOGENERATE today()-date#(20011101,'YYYYMMDD');
LEFT JOIN (FiscalCalendar)
LOAD
"master_date",
date(monthstart(master_date),'MMM YY') AS "MonthFisical",
date(monthstart(master_date),'MMM') AS "MonthFisical_MonthTitle",
date(yearstart(master_date,1,11),'YYYY') AS "YearFiscal",
month(master_date)-month(num(today(1))) AS FiscalMonthsElapsed,
YearToDate(master_date, 0,11)*-1 AS YTD_FLAG_Fiscal,
YearToDate(master_date,-1,11)*-1 AS LY_YTD_FLAG_Fiscal
RESIDENT FiscalCalendar;
To see screen shots:
http://community.qlikview.com/message/219912#219912
Thank you for taking the time to look at this issue.
business-intelligence qlikview
I hope this contains enough information/context
– octopus_guy
Jun 12 '12 at 15:04
Seems that community.qlikview.com/message/219912#229267 contains a solution.
– smartmeta
Sep 16 '13 at 7:59
add a comment |
This forum seems to be more geared toward more programming issues but I would be interested if someone would look at the logic issue in the below link. My issue is how to calculate the previous 12 month total using the calendar already defined. I have found very few examples of this on the QV community. Please see the below link for more details. I would be willing to look at a SQL or a QV script solution.
Our fiscal year runs Nov to Oct. I would like the end user to select Year and a chart to display the last rolling 12 month margin. I have had issues getting my total to accumulate for previous months. My goal would be for it look similar to the Rolling 12 Month Total - GP column in the manually caculated Excel image 'Goal'. (look at QV link for screenshot)
Rolling Margin equation: my attempt to use Set Analysis to make a rolling avg equation
=Sum({<master_date={'>=$(=MonthStart(Max(master_date), -12))<=$(=MonthEnd(Max(master_date)))'}>}
MasterCalendar
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
MasterCalendar: this uses master_date to connect items together. This an fiscal calendar are hard to put together with rolling avg
LOAD
TempDate AS master_date,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
If(Num(TempDate) >= $(vYearStart) AND Num(TempDate) < $(vMonthNow), -1, 0) AS YTD,
If(Num(TempDate) >= $(vYearStartLY) AND Num(TempDate) < $(vMonthNowLY), -1, 0) AS LY_YTD,
Year2Date(TempDate) * -1 AS YTD_Flag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LY_YTD_Flag
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
FiscalCalander> This defines our fiscal year
FiscalCalendar:
LOAD date(date#(20011101,'YYYYMMDD')+recno(),'MM/DD/YY') AS "master_date"
AUTOGENERATE today()-date#(20011101,'YYYYMMDD');
LEFT JOIN (FiscalCalendar)
LOAD
"master_date",
date(monthstart(master_date),'MMM YY') AS "MonthFisical",
date(monthstart(master_date),'MMM') AS "MonthFisical_MonthTitle",
date(yearstart(master_date,1,11),'YYYY') AS "YearFiscal",
month(master_date)-month(num(today(1))) AS FiscalMonthsElapsed,
YearToDate(master_date, 0,11)*-1 AS YTD_FLAG_Fiscal,
YearToDate(master_date,-1,11)*-1 AS LY_YTD_FLAG_Fiscal
RESIDENT FiscalCalendar;
To see screen shots:
http://community.qlikview.com/message/219912#219912
Thank you for taking the time to look at this issue.
business-intelligence qlikview
This forum seems to be more geared toward more programming issues but I would be interested if someone would look at the logic issue in the below link. My issue is how to calculate the previous 12 month total using the calendar already defined. I have found very few examples of this on the QV community. Please see the below link for more details. I would be willing to look at a SQL or a QV script solution.
Our fiscal year runs Nov to Oct. I would like the end user to select Year and a chart to display the last rolling 12 month margin. I have had issues getting my total to accumulate for previous months. My goal would be for it look similar to the Rolling 12 Month Total - GP column in the manually caculated Excel image 'Goal'. (look at QV link for screenshot)
Rolling Margin equation: my attempt to use Set Analysis to make a rolling avg equation
=Sum({<master_date={'>=$(=MonthStart(Max(master_date), -12))<=$(=MonthEnd(Max(master_date)))'}>}
MasterCalendar
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
MasterCalendar: this uses master_date to connect items together. This an fiscal calendar are hard to put together with rolling avg
LOAD
TempDate AS master_date,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
If(Num(TempDate) >= $(vYearStart) AND Num(TempDate) < $(vMonthNow), -1, 0) AS YTD,
If(Num(TempDate) >= $(vYearStartLY) AND Num(TempDate) < $(vMonthNowLY), -1, 0) AS LY_YTD,
Year2Date(TempDate) * -1 AS YTD_Flag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LY_YTD_Flag
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
FiscalCalander> This defines our fiscal year
FiscalCalendar:
LOAD date(date#(20011101,'YYYYMMDD')+recno(),'MM/DD/YY') AS "master_date"
AUTOGENERATE today()-date#(20011101,'YYYYMMDD');
LEFT JOIN (FiscalCalendar)
LOAD
"master_date",
date(monthstart(master_date),'MMM YY') AS "MonthFisical",
date(monthstart(master_date),'MMM') AS "MonthFisical_MonthTitle",
date(yearstart(master_date,1,11),'YYYY') AS "YearFiscal",
month(master_date)-month(num(today(1))) AS FiscalMonthsElapsed,
YearToDate(master_date, 0,11)*-1 AS YTD_FLAG_Fiscal,
YearToDate(master_date,-1,11)*-1 AS LY_YTD_FLAG_Fiscal
RESIDENT FiscalCalendar;
To see screen shots:
http://community.qlikview.com/message/219912#219912
Thank you for taking the time to look at this issue.
business-intelligence qlikview
business-intelligence qlikview
edited Jun 9 '16 at 10:54


Chris J
764523
764523
asked Jun 12 '12 at 13:48
octopus_guyoctopus_guy
4113
4113
I hope this contains enough information/context
– octopus_guy
Jun 12 '12 at 15:04
Seems that community.qlikview.com/message/219912#229267 contains a solution.
– smartmeta
Sep 16 '13 at 7:59
add a comment |
I hope this contains enough information/context
– octopus_guy
Jun 12 '12 at 15:04
Seems that community.qlikview.com/message/219912#229267 contains a solution.
– smartmeta
Sep 16 '13 at 7:59
I hope this contains enough information/context
– octopus_guy
Jun 12 '12 at 15:04
I hope this contains enough information/context
– octopus_guy
Jun 12 '12 at 15:04
Seems that community.qlikview.com/message/219912#229267 contains a solution.
– smartmeta
Sep 16 '13 at 7:59
Seems that community.qlikview.com/message/219912#229267 contains a solution.
– smartmeta
Sep 16 '13 at 7:59
add a comment |
1 Answer
1
active
oldest
votes
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.
Examples:
xlsx
QVW
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f10998061%2fqlikview-rolling-12-month-fiscal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Examples:
xlsx
QVW
add a comment |
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.
Examples:
xlsx
QVW
add a comment |
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.
Examples:
xlsx
QVW
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.
Examples:
xlsx
QVW
answered Dec 12 '14 at 9:59


MattMatt
10.8k216698
10.8k216698
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f10998061%2fqlikview-rolling-12-month-fiscal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Avr9StIRhm,r6,Qf5vFNOFDmja553 nkWK,4 5GPJY0bg9wnAJIipU2O63db5FZ39RxTJC5MMSg,nmMiSQJZ,w
I hope this contains enough information/context
– octopus_guy
Jun 12 '12 at 15:04
Seems that community.qlikview.com/message/219912#229267 contains a solution.
– smartmeta
Sep 16 '13 at 7:59