Qlikview Rolling 12 Month Fiscal

Multi tool use
Multi tool use












8















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.










share|improve this question

























  • 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
















8















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.










share|improve this question

























  • 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














8












8








8


0






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.










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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



















  • 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












1 Answer
1






active

oldest

votes


















0














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






share|improve this answer























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    0














    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






    share|improve this answer




























      0














      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






      share|improve this answer


























        0












        0








        0







        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






        share|improve this answer













        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







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 12 '14 at 9:59









        MattMatt

        10.8k216698




        10.8k216698






























            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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
            XCS cGny,F8HyfjD,s5np1V6Ehw0 g9pj,nTCZ UN,gr8a e 3Y,P2m4D0knHY7,c,GwsnClWTeCXqw1

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas