61 Replies Latest reply: Oct 29, 2014 2:02 PM by Josh Good RSS

Creating A Master Calendar

Josh Good

This videos show how to create a Master Date Calendar in QlikView.  The script mentioned in the video is below.

 

http://youtu.be/ScdIQvWzVFs

 

 

 

QuartersMap:
MAPPING LOAD 
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
               min(OrderDate) as minDate,
               max(OrderDate) as maxDate
Resident Orders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
               TempDate AS OrderDate,
               week(TempDate) As Week,
               Year(TempDate) As Year,
               Month(TempDate) As Month,
               Day(TempDate) As Day,
               YeartoDate(TempDate)*-1 as CurYTDFlag,
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
  • Creating A Master Calendar
    anthonyb

    Thanks! For anyone looking to learn the fundamentals of using a master calendar, I would definitely recommend watching this video.

    • Re: Creating A Master Calendar
      anthonyb

      Josh, I've tried to implement the Master Calendar after having watched your video, and I feel like I understand how everything works, but I'm getting an error on reload with the temp table/autogenerate. Any idea why that would be?

       

      Here is my error:

       

      Field not found - <<=>

      TempCalendar:

      LOAD

                      + Iterno()-1 As Num,

                     Date( + IterNo() - 1) as TempDate

                     AutoGenerate 1 While  + IterNo() -1 <=

      • Re: Creating A Master Calendar
        Josh Good

        It looks like you are not generating values for varMinDate (and like also for varMaxDate) for some reason.  Try using the degubber and running the script using 'Step' to confirm this (see image which show what you should be seeing if you are generating values).  The reason for this could either be your Temp table is empty or there is some sort of syntax error on the line that sets the variable varMinDate (line 37 of the image below).

         

        2012-05-24_0014.png

         

        • Re: Creating A Master Calendar
          anthonyb

          That's what I thought too Josh, so here is what I did.

           

          To try and debug, I removed everything but the below:

           

          QuartersMap:
          MAPPING LOAD
          rowno() as Month,
          'Q' & Ceil (rowno()/3) as Quarter
          AUTOGENERATE (12);

          Temp:
          Load
                         min(PerformanceDate) as minDate,
                         max(PerformanceDate) as maxDate
          Resident DimPerformanceDate;

           

          When I run the above, and display mindate and maxdate in a listbox it definitely contains values.

           

          But once it gets to that autogenerate line it can't find them...

           

          Also, another error I see in debugger is that monthstart must contain two values. (Not sure)

           

          Does your script only work on a certain version? Because these problems are really strange.

          I'm running QV9. Figured I'd throw that out there before I keep trying to troubleshoot something that may not work.

           

          Thanks for your response,

          -Anthony

          • Re: Creating A Master Calendar
            Josh Good

            Hi Anthony,

             

            Yes the script should work in V9.  I origionally started using in V9 myself.  I'm a bit at a lost as to why you are running into an error.  The only thing I can think of is that your data is not being recongized as dates by QlikView.  Maybe see if you can get the script to work with different data (to rule that out).

             

            Regards,

            Josh

            • Creating A Master Calendar
              m m

              Hi Josh

               

              Your master calander calculates quarter

              q1 = jan to march

              q2= apr to jun

               

              what if the quarter starts from dec.

              q1=dec - feb

              q2= mar - may

               

               

              how do you make changes to master calander to accomodate that.

               

              please help

              thanks

              • Creating A Master Calendar
                Rob Wunderlich

                We are all coding MasterCalendars over and over again. It's an interesting excercise to learn scripting, but if you just want to get the calendar done, consider using Qlikview Components http://qlikviewcomponents.org, the free open source QV script library.

                http://qlikviewnotes.blogspot.com/2012/01/easy-period-analysis-using-qlikview.html

                 

                -Rob

              • Re: Creating A Master Calendar
                Rob Wunderlich

                How about:

                 

                'Q' & Ceil (month(AddMonths(Tempdate,1)/3) as Quarter

                 

                -Rob

                http://robwunderlich.com

                • Re: Creating A Master Calendar
                  m m

                  Thanks Josh. I tried your script it gave me error.

                   

                  "Generic tables must contain at least 3 fields"

                   

                   

                   

                   

                  Rob.

                  I have tried using calander from QV components. However how do you adjust quarter to start at different month using components.

                   

                   

                  thanks

                  • Re: Creating A Master Calendar
                    Josh Good

                    Sorry about that.  I failed to name the second column in the inline load.   I have corrected it in my previous post.

                     

                    -Josh

                     

                    The bold is what was added:

                     

                    QuartersMap:

                    MAPPING LOAD * Inline [

                    Month, Q

                    1, Q1

                    • Re: Creating A Master Calendar
                      m m

                      Thanks Josh.

                       

                      It works. however when i am getting weird dates.

                       

                      19981203    (YYYYMMDD)   Source

                       

                       

                      however when i bring that date and try passing thru date function, i get weird numbers.

                       

                      year(saledate)as year,

                       

                       

                      I get numbers that is unlike year.

                      56606

                       

                       

                      Due to this my master calander function is not working properly.

                      • Re: Creating A Master Calendar
                        Josh Good

                        I believe the issue is your dates are not in a format that QlikView is expecting you can either change the default format or change the date to a format QlikView is expecting.

                         

                        To change the default format you need to change the statement that sets this.  This is typicaly line 7 on the first tab of the script.

                        e.g. change  SET DateFormat='M/D/YYYY';  to SET DateFormat='YYYYMMDD';

                         

                        Alternatively (my preference), change the format of the source data by parsing out the date into its components and putting it back together when you load it.  The expression would look something like this:

                         

                        MakeDate(Left(SourceDate, 4), Right(Left(SourceDate,2),6), Right(SourceDate,2)) as Date

                        • Creating A Master Calendar
                          m m

                          Thanks Josh & Rob for your help.

                          • Re: Creating A Master Calendar
                            Simon Goodman

                            I have just seen this and I am in the process of creating a fiscal calendar.

                             

                            How did you get CurYTDFlag and LastYTDFlag to start at the beginning of the fiscal year? I have tried a couple of things unsucessfully.

                             

                            Similarly how did you get Week to realign with the fiscal months? Or how did you create a fiscal week in the script?

                            • Re: Creating A Master Calendar
                              Rob Wunderlich

                              Creating fiscal fields is all about using the FirstMonthOfYear parm in the various date functions. For example, see the help for InYearTodate().

                               

                              -Rob

                              • Re: Creating A Master Calendar
                                Simon Goodman

                                Hi Rob

                                 

                                Thanks for that. I understand the concept but failed in execution. I think

                                I can not see the wood for the trees. 1 is as per Help and 2 is one of my

                                failed  efforts.

                                 

                                 

                                   1. inyeartodate(date, basedate , shift )

                                   2. inyeartodate(TempDate, $(varToday), 0 )

                                    

                                • -1 AS CurYTDFlag

                                 

                                Please can you help?

                                Simon

                                On Tue, Jun 12, 2012 at 3:17 PM, Rob Wunderlich <

                                • Re: Creating A Master Calendar
                                  Rob Wunderlich

                                  Can you post yout script?

                                  • Re: Creating A Master Calendar
                                    Simon Goodman

                                    Rob below is the calendar script where it is failing. I also attached the

                                    Error screenshot.

                                     

                                    Simon

                                     

                                    //*****Fiscal Year Quarter Map*****

                                    QuartersMap:

                                    MAPPING LOAD * Inline [

                                    Month, Q

                                    1, Q2

                                    2, Q2

                                    3, Q2

                                    4, Q3

                                    5, Q3

                                    6, Q3

                                    7, Q4

                                    8, Q4

                                    9, Q4

                                    10, Q1

                                    11, Q1

                                    12, Q1

                                    ];

                                     

                                    LET varMinDate = Num(MakeDate(2010,10,1));                      //

                                    first date

                                    LET varMaxDate = Floor(num(Today()));                      // will find

                                    the last record

                                    LET varToday = Num(today());

                                    // Finds todays date

                                     

                                    //*************** Temporary Calendar ***************

                                    TempCalendar:

                                    LOAD

                                                $(varMinDate) + rowno() - 1 AS Num,

                                                date($(varMinDate) + rowno() - 1) AS TempDate

                                    AUTOGENERATE 1

                                                While $(varMinDate)+IterNo()-1<= $(varMaxDate);

                                     

                                    //*************** Master Calendar ***************

                                    MasterCalendar:

                                    LOAD

                                                             TempDate AS PostingDate,

                                                             Week(TempDate) AS Week,

                                                             Year(TempDate) AS Year,

                                                             Month(TempDate) AS Month,

                                     

                                                             Day(TempDate) AS Day,

                                                             Weekday(TempDate) AS WeekDay,

                                     

                                                             ApplyMap('QuartersMap', month(TempDate), Null()) as

                                    Quarter,

                                                             yearname ( TempDate, 0, 10 )   as FiscalYear,

                                                             Date(monthstart(TempDate), 'MMM-YYYY') AS

                                    MonthYear,

                                                             Month(TempDate)&'-'&right(year(TempDate),10) as

                                    FiscalMonthYear,

                                                             Week(TempDate)&'-'&Year(TempDate) AS WeekYear,,

                                                             inyear(TempDate, Monthstart($(varMaxDate)),-1) as

                                    RC12,

                                                             inyeartodate(TempDate, $(varToday), 0 [,

                                    first_month_of_year = 10]) * -1   AS CurYTDFlag,

                                     

                                                              inyeartodate(TempDate, $(varToday), -1 [,

                                    first_month_of_year = 10]) * -1 AS LastYTDFlag

                                     

                                    RESIDENT TempCalendar

                                    ORDER BY TempDate ASC;

                                     

                                    DROP TABLE TempCalendar;

                                     

                                    On Wed, Jun 13, 2012 at 6:06 AM, Rob Wunderlich <

                                    • Re: Creating A Master Calendar
                                      Felim Shanaghy

                                      This line you have a double comma

                                       

                                      Week(TempDate)&'-'&Year(TempDate) AS WeekYear,,

                                      • Re: Creating A Master Calendar
                                        Simon Goodman

                                        This is a typo and not in the script.

                                         

                                        On Wed, Jun 13, 2012 at 11:39 AM, Felim Shanaghy <

                                        • Re: Creating A Master Calendar
                                          Felim Shanaghy

                                          The code below works, its related to : -

                                           

                                          inyeartodate(TempDate, $(varToday), 0 [,

                                          first_month_of_year = 10]) * -1   AS CurYTDFlag,

                                           

                                                                    inyeartodate(TempDate, $(varToday), -1 [,

                                          first_month_of_year = 10]) * -1 AS LastYTDFlag

                                           

                                          The above are incorrectly formatted

                                           

                                           

                                          //*****Fiscal Year Quarter Map*****

                                          QuartersMap:

                                          MAPPING LOAD * Inline [

                                          Month, Q

                                          1, Q2

                                          2, Q2

                                          3, Q2

                                          4, Q3

                                          5, Q3

                                          6, Q3

                                          7, Q4

                                          8, Q4

                                          9, Q4

                                          10, Q1

                                          11, Q1

                                          12, Q1

                                          ];

                                           

                                          LET varMinDate = Num(MakeDate(2010,10,1));                  //first date

                                          LET varMaxDate = Floor(num(Today()));                       // will find the last record

                                          LET varToday = Num(today());                                                                                // Finds todays date

                                           

                                          //*************** Temporary Calendar ***************

                                          TempCalendar:

                                          LOAD $(varMinDate) + rowno() - 1 AS Num,

                                               date($(varMinDate) + rowno() - 1) AS TempDate

                                          AUTOGENERATE 1

                                          While $(varMinDate)+IterNo()-1<= $(varMaxDate);

                                           

                                          //*************** Master Calendar ***************

                                          MasterCalendar:

                                          LOAD TempDate AS PostingDate,

                                               Week(TempDate) AS Week,

                                               Year(TempDate) AS Year,

                                               Month(TempDate) AS Month,

                                                     Day(TempDate) AS Day,

                                               Weekday(TempDate) AS WeekDay,

                                               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                                               yearname ( TempDate, 0, 10 ) as FiscalYear,

                                               Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

                                               Month(TempDate)&'-'&right(year(TempDate),10) as FiscalMonthYear,

                                               Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

                                               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

                                               inyeartodate(TempDate, $(varToday), 0, 10) * -1   AS CurYTDFlag,

                                               inyeartodate(TempDate, $(varToday), -1, 10) * -1 AS LastYTDFlag

                                          RESIDENT TempCalendar

                                          ORDER BY TempDate ASC;

                                           

                                          DROP TABLE TempCalendar;

                        • Re: Creating A Master Calendar
                          Beanz

                          Hi all,

                           

                          I've used this master calendar script many times, in other apps, without any issues.

                          But in my attached example, I seem to be getting the 'Field <<> not found' error.

                           

                          It's definitely the formatting, as I've Stepped through the script in the debugger.

                           

                          I've tried formatting the date field in different ways.

                           

                          Any ideas?

                           

                          Cheers

                  • Re: Creating A Master Calendar
                    Rob Wunderlich

                    If you want to offset your entire Calendar -- a fiscal calendar -- set the FirstMonth parm to 12 in your call to Qvc.Calendar or Qvc.CalendarFromField. For example:

                     

                    CALL Qvc.CalendarFromField('OrderDate', 'Fiscal Calendar', 'Fiscal ', '12');

                     

                    If instead you want a standard calendar, but want to offset just the Quarter field, use the new code extension feature available in Qvc 4.0.

                     

                    SET Qvc.Global.Extension.Directory=C:\local\QvcExtensions;

                     

                    and the contents of C:\local\QvcExtensions\CalendarExtFields.qvs would be:

                     

                    ,'Q' & Ceil (month(AddMonths(Date,1)/3) as [$(_fieldPrefix)OffsetQuarter]

                     

                    If you want to replace the standard Quarter field with the offset one, follow the calendar generation with:

                     

                    DROP FIELD Quarter;

                    RENAME FIELD OffsetQuarter TO Quarter;

                     

                    -Rob

                    • Re: Creating A Master Calendar
                      harsh704

                      Hi Rob,

                       

                      It would be very helpful if you could please explain the following.

                       

                      I am trying to use a Fiscal year calendar where the year starts iN December andnI have used the following for Current YTD and Prior YTD.

                       

                      inyeartodate(TempDate, $(varToday), 0, 12) * -1  AS CurYTDFlag,

                      inyeartodate(TempDate, $(varToday), -1, 12) * -1 AS LastYTDFlag

                       

                      The only problem is that as of today my data is only for YTD end of May. When it calculates the Prior Year it counts data al lthe way to today's date last year. How do i make the above script to stop at end of previous month - which in this case is May 2013. I just want the above to calculate the YTD from Dec 2012 to May 2013 and Dec 2011 to may 2012.

                       

                      Thanks,

                       

                      Harsha

              • Re: Creating A Master Calendar
                vutukuru siri

                You can use below code, for getting quarters with differant months.

                We can change order of months and quarters in the way we needed.

                Mapinline:

                mapping Load * inline [month,Quarter

                1,Q1

                2,Q1

                3,Q1

                4,Q2

                5,Q2

                6,Q2

                7,Q3

                8,Q3

                9,Q3

                10,Q4

                11,Q4

                12,Q4];

      • Re: Creating A Master Calendar
        Pavan Kumar

        Just try these.

         

        load * inline [

         

        Date(orderdate) as Date,

        Month(orderdate) as Month,

        Year(orderdate) as Year,

         

        if(num(Month(orderdate)) >=4 and num(Month(orderdate))<=6, 'Q1',

        if(num(Month(orderdate)) >=7 and num(Month(orderdate))<=9, 'Q2',

        if(num(Month(orderdate)) >=10 and num(Month(orderdate))<=12, 'Q3',

        if(num(Month(orderdate)) >=1 and num(Month(orderdate))<=3, 'Q4')))) as Quarter,

         

        ];

         

        For output of this script, please open the attachmnet.

         

        Still having any doubts please let me know....

      • Re: Creating A Master Calendar
        Marko Rakar

        Just a quick note;

         

        I have spent good deal of time to understand why my master calendar is not working and the error I got was the error you mentioned above.

         

        The script is absolutely ok, it is just that the because of complexity of my data, I am using "QUALIFY" statement previously in the script and in order to make master calendar work you either have to adress that and correct references to exact and full field names or, just in front of your master calendar script add "UNQUALIFY *;"

         

        I hope this helps, I spent too much time figuring this out and I am surprised that nobody else mentioned this rookie mistake.

  • Re: Creating A Master Calendar
    Josh Good

    Hi,

     

    To do this you will need to change the Mapping table to match your quarters.  I would either drive it off fiscal year definitions from a data source or using an inline load. The inline load would look like this:

     

     

     

    QuartersMap:

    MAPPING LOAD * Inline [

    Month, Q

    1, Q1

    2, Q1

    3, Q2

    4, Q2

    5, Q2

    6, Q3

    7, Q3

    8, Q3

    9, Q4

    10, Q4

    11, Q4

    12, Q1

    ];

     

    You may also want to define a Fiscal Year as well.  In the load statement that defined the Master Calendar table you could add a line something like this:

     

    If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

     

    Putting it all together your script would look something like below.

     

    I hope that helps!

     

    QuartersMap:

    MAPPING LOAD * Inline [

    Month, Q

    1, Q1

    2, Q1

    3, Q2

    4, Q2

    5, Q2

    6, Q3

    7, Q3

    8, Q3

    9, Q4

    10, Q4

    11, Q4

    12, Q1

    ];

     

     

    Temp:

    Load

                   min(OrderDate) as minDate,

                   max(OrderDate) as maxDate

    Resident Orders;

     

    Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

    Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

    DROP Table Temp;

     

    TempCalendar:

    LOAD

                   $(varMinDate) + Iterno()-1 As Num,

                   Date($(varMinDate) + IterNo() - 1) as TempDate

                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

     

    MasterCalendar:

    Load

                   TempDate AS OrderDate,

                   week(TempDate) As Week,

                   Year(TempDate) As Year,

                   Month(TempDate) As Month,

                   Day(TempDate) As Day,

                   YeartoDate(TempDate)*-1 as CurYTDFlag,

                   YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

                   inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

                   date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

                   ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                   If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

                   Week(TempDate) & '-' & Year(TempDate) as WeekYear,

                   WeekDay(TempDate) as WeekDay

    Resident TempCalendar

    Order By TempDate ASC;

    Drop Table TempCalendar;

    • Creating A Master Calendar
      m m

      Can you please explain the below statement

       

       

      If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

       

       

      thanks

    • Re: Creating A Master Calendar
      harsh704

      Hi Josh,

       

      It would be very helpful if you could please explain the following. I am trying to use a Fiscal year calendar where the year starts iN December andnI have used the following for Current YTD and Prior YTD.

       

       

           inyeartodate(TempDate, $(varToday), 0, 12) * -1   AS CurYTDFlag,

           inyeartodate(TempDate, $(varToday), -1, 12) * -1 AS LastYTDFlag

       

      The only problem is that as of today my data is only for YTD end of May. When it calculates the Prior Year it counts data al lthe way to today's date last year. How do i make the above script to stop at end of previous month - which in this case is May 2013.

       

      I just want the above to calculate the YTD from Dec 2012 to May 2013 and Dec 2011 to may 2012.

       

      Thanks,

       

      Harsha

  • Re: Creating A Master Calendar
    Jaime Aguilar

    For some reason when I use the loop: AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); it does not show some months in the correct order whenever I select them. To solve that I used the next line instead of the while loop:

     

    $(varMaxDate) - $(varMinDate) + 1;

     

    Anyway, this is such a good Calendar example

  • Re: Creating A Master Calendar
    Iain Rutgers

    Hey Josh, I just noticed a bug with the script. If a week goes over into the new year it gets the incorrect year/week assigned.

     

    Take for example Jan 1, 2012

     

    in your code Week(TempDate) & '-' & Year(TempDate) as WeekYear,

     

    The week returns 52, because it is the 52nd week of 2011, week 1 of 2012 starts on the following Monday. The year argument returns 2012. so the end result is "52-2012" which is really the end of 2012 instead of 2011.

     

    To get around this I use the weekstart argument for the weeks so it always looks at the Monday of the week.

    e.g. Week(weekstart(TempDate)) & '-' & Year(weekstart(TempDate)) as WeekYear,

     

    Cheers,

    • Re: Creating A Master Calendar
      Josh Good

      Thanks Iain!  I have updated the script in the original post.

       

      -Josh

    • Re: Creating A Master Calendar
      Rob Wunderlich

      I think a better solution may be to use the WeekYear() function.

      WeekYear(tempdate)

      will return 2011.

       

      I only know about this because I made the same mistake in the Qlikview Components calendar and someone sent me a correction.

       

      -Rob

      • Re: Creating A Master Calendar
        Josh Good

        Thanks Rob.  Sometimes I'm amazed at the number of functions we have to in QV!  I have updated the script.

         

        -Josh

        • Re: Creating A Master Calendar
          m m

          Hi Rob

           

          So in my case I am doing master calander where fiscal starts from sep to Aug. I have alligned quarters accordingly for inline. How do I make changes to fiscal year.

           

          QuartersMap:

          MAPPING LOAD * Inline [

          Month, Q

          1, Q2

          2, Q2

          3, Q3

          4, Q3

          5, Q3

          6, Q4

          7, Q4

          8, Q4

          9, Q1

          10, Q1

          11, Q1

          12, Q2

          ];

           

           

          What should be the change to below code:

          If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

           

           

          Should it be adjusted to.below?

          If (Month(TempDate) = 12, Year(TempDate) + 8, Year(TempDate)) as FiscalYear,

           

           

          Do I need to make any other change for my Calander to function.

          • Re: Creating A Master Calendar
            Josh Good

            My recommendation is to use the AddMonth Function.

             

            Year(AddMonth(TempDate,3)) as FiscalYear

             

            This will take a date in Sept 2012 and return 2013.  If you wish you can add this line to the script for the MasterCalendar table and retain the line that just has the calendar year so you will be able to filter on Fiscal Year and/or Calendar Year.

             

            You may also want to add line for the fiscal month number but I would also retain the calendar month.  This will enable you to sort or the Month field in the correct fiscal order (i.e. sept first)

             

            ....

            Month(TempDate) As Month,

            Num(Month(AddMonth(TempDate,3))) as FiscalMonthNumber,

            ....

  • Re: Creating A Master Calendar
    plataniasangel

    Hi I am truing to implement the master calendar but I have to load data from two tables in an access file one containing dates from 2011 and the other from 2012.

     

    I use the following code, but I only get the 2011 data to apear, what should I change?

     

    ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Users\t42386\Documents\GR Data\ Pick Detail 2011-12.accdb];

     

     

    OrderHeader:

    LOAD Date,

        Material,

        `Pallet_ID`,

        `Picked Cases`,

        PickType,

        `Plant_NO`,

        Route;

     

     

    SQL SELECT *

    FROM ` Pick Detail 2011`;

     

     

    OrderHeader:

    LOAD Date,

        Material,

        `Pallet_ID`,

        `Picked Cases`,

        PickType,

        `Plant_NO`,

        Route;

     

    SQL SELECT *

    FROM `Pick Detail 2012`;

  • Re: Creating A Master Calendar
    Markus König

    Nice Calendar, but I have a little bit of troubel to configure the skript to make the calendar minute based.

  • Re: Creating A Master Calendar
    Lisandro Lopez

    Hi everybody, Im using QLIKVIEW 11

    I saw this thread and i tried to use this code in my script, but after loading a lot of data, script "failed" without error.

    Its not a RAM problem because i also run it in a 8GM machine and same result.

    ¿Anyone can help me with this? I don't know where is the problem..

    My table has 1.000.000 of registers... but this TempCalendar loads like 40 millon..and there is the problem i think

    Format date is 2009-11-26 12:21:00.000 in SQL

    But in qlikview appears like  26/11/2009 12:21:00 AM

    here is an image

    asd.JPG

    It continues loading and in 45 imillon fails..

    This is the code

     

     

    QuartersMap:

    MAPPING LOAD

    rowno() as Month,

    'Q' & Ceil (rowno()/3) as Quarter

    AUTOGENERATE (12);

     

     

    Temp:

    Load

                   min(Pedido_Fecha) as minDate,

                   max(Pedido_Fecha) as maxDate

    Resident Pedidos;

     

     

    Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

    Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

    DROP Table Temp;

     

     

    TempCalendar:

    LOAD

                   $(varMinDate) + Iterno()-1 As Num,

                   Date($(varMinDate) + IterNo() - 1) as TempDate

                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

     

     

    MasterCalendarPedidos:

    Load

                   TempDate AS Pedido_Fecha,

                   week(TempDate) As PedidoWeek,

                   Year(TempDate) As PedidoYear,

                   Month(TempDate) As PedidoMonth,

                   Day(TempDate) As PedidoDay,

                   YeartoDate(TempDate)*-1 as PedidoCurYTDFlag,

                   YeartoDate(TempDate,-1)*-1 as PedidoLastYTDFlag,

                   inyear(TempDate, Monthstart($(varMaxDate)),-1) as PedidoRC12,

                   date(monthstart(TempDate), 'MMM-YYYY') as PedidoMonthYear,

                   ApplyMap('QuartersMap', month(TempDate), Null()) as PedidoQuarter,

                   Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as PedidoWeekYear,

                   WeekDay(TempDate) as PedidoWeekDay

    Resident TempCalendar

    Order By TempDate ASC;

    Drop Table TempCalendar;

     

     

    Thanks

  • Re: Creating A Master Calendar
    Kyle McNamara

    I am working with a customer where their Q1 is Oct/Nov/Dec… the really nice master calendar above returns Q1 = Jan/Feb/March... what would I have to do to have Q1 = Oct/Nov/Dec and then Q2 = Jan/Feb/Mar etc etc.. I am using 11.2 btw.

     

    I messed around with the scrip  a bunch to see if I could make it happen... no luck so far and am crunched for time.

     

    Tx!

    k

    • Re: Creating A Master Calendar
      Josh Good

      You can use the AddMonths function to move the calendar date forward or backwards to be the correct fiscal date.  In your case you will want to add three months to the date so Oct 1st moves to the first day of the year (Jan 1st) from there you can apply the vaious funcitons to make your calendar.  Note you can keep both fiscal and calendar dates in the same table.

       

      So for FiscalQuarter it would look like this....

       

       

      MasterCalendar:

      Load

                    ...

                     ApplyMap('QuartersMap', month(AddMonths(TempDate,3)), Null()) as FiscalQuarter,

                    ...

      Resident TempCalendar

  • Re: Creating A Master Calendar
    Hannan Tariq

    Hey Josh,

    I'm a newbie. The original script you shared is not working for me. It gives the following errors:

    1) Table Not Found Temp:

    2) Field not found - <<=>

       TempCalendar:

    3)Error in expression: MonthStart takes 1-2 parameters

       MasterCalendar:

     

    Can you please guide me about this?

  • Re: Creating A Master Calendar
    Tony O'Rourke

    Hi Josh,

     

    Love the calendar and have been using it with no issues for a while now.

    But,

    I have a new app where I need to show sum of a measure from last week.

    Using Max(Week) is giving me "52" (obviously from 2013), but what I need to see is week 3 from 2014.

     

    Any ideas for me ?

    I'm trying to show the most active customer last week basically.

     

    Thanks

    T

    • Re: Creating A Master Calendar
      Josh Good

      Tony,

       

      I would setup a flag in the calendar that flags all dates that are 'last week'.  First define what today is with a variable and then check and see the last week dates.  so something like this:

       

      Let vToday = Today(0);

       

      ///A Bunch of Script

      ///The Beginning of the Calendar Script...

       

      MasterCalendar: 

      Load 

                     TempDate AS OrderDate, 

                     //....

                    if (WeekStart(TempDate) = WeekStart($(vToday)-7), 1) as LastWeekFlag

                     //...

      Resident TempCalendar

       

      //The Rest of the Calendar Script

       

      Hope that helps,

      Josh

       

      PS I like your Mr. T avatar. "I pity the fool who doesn't use QlikView!"

  • Re: Creating A Master Calendar
    Marie-Joseph Gomis

    Hi Josh,

     

    Am new with Qlikview and have little problem, hope i find help here.

     

    So, i created a MasterCalendar liked showed in the tutorial. (Thx by the way for this)

    Then i created two variables FromDate and ToDate but when i click on the Calendar I only see 1 Month instead of all the dates shows in the preview of the MasterCalendar. I would like to show all months and days from my Calender, how can i do this?

    The two variables have as default value 'Varmaxdate' from the Mastercalendar script.

     

    Thanks in advance for your help.

    MJ

  • Re: Creating A Master Calendar
    Gregor Presetnik

    Hi Josh,

    This is awesome for the fact data.

    How do you suggest we use dates for the forecast calendar?

     

    Thx

    G

    • Re: Re: Creating A Master Calendar
      Josh Good

      Hi Gregor,

       

      I'm not sure what is different about your 'forecast calendar'.  This is not reason you can't have multiple date fields (and hence multiple calendars) in the same data model.  If you do that then you will want to name each calendar and the fields in it appropriately.  So you could have a "Forecast Calendar" and "Shipped Calendar" etc.

       

      To do this you would repeat the script for each calendar and adjust the field names as appropriate - this is the easiest way.

       

      A more elegant way would be to use the script below to loop through each calendar you want to create and drive which calendar is created from the inline table (or an external table).  I find this approach to be a bit 'touchy' and often requires a bit of trouble shooting to get it to work just right.

       

      -Josh

      Qlik

       

      CalendarNames:

      Load * Inline [

      CalendarName, Table, CalendarNameSpaces, CalendarNameNoSpaces,

      ActualEnd, Data, Actual End, Actual End

      EstimatedEnd, Data, Estimated End, Estimated End

      Start, Data, Start, Start,

      ];

       

       

      QuartersMap:

      MAPPING LOAD

      rowno() as Month,

      'Q' & Ceil (rowno()/3) as Quarter

      AUTOGENERATE (12);

       

      For i = 0 to (NoOfRows('CalendarNames')-1);

       

      Let varCalendarNameNoSpaces = Peek('CalendarName', $(i), 'CalendarNames');

      Let varCalendarNameSpaces = Peek('CalendarNameSpaces', $(i), 'CalendarNames');

      Let varCalendarFromTable = Peek('Table', $(i), 'CalendarNames');

      Set varDateField = $(varCalendarNameSpaces) Date;

       

      Temp:

      Load

      min([$(varDateField)]) as minDate,

      max([$(varDateField)]) as maxDate

      Resident $(varCalendarFromTable);

       

                      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

      DROP Table Temp;

       

                      TempCalendar:

      LOAD

      $(varMinDate) + Iterno()-1 As Num,

      Date($(varMinDate) + IterNo() - 1) as TempDate

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

       

                      $(varCalendarNameNoSpaces)Calendar:

      Load

      TempDate AS [$(varCalendarNameSpaces) Date],

      week(TempDate) As [$(varCalendarNameSpaces) Week],

      Year(TempDate) As [$(varCalendarNameSpaces) Year],

      Month(TempDate) As [$(varCalendarNameSpaces) Month],

      Day(TempDate) As [$(varCalendarNameSpaces) Day],

      //YeartoDate(TempDate)*-1 as CurYTDFlag,

      //YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

      // inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

      date(monthstart(TempDate), 'MMM-YYYY') as [$(varCalendarNameSpaces) Month-Year],

      ApplyMap('QuartersMap', month(TempDate), Null()) as [$(varCalendarNameSpaces) Quarter],

      Week(TempDate) & '-' & Year(TempDate) as [$(varCalendarNameSpaces) Week-Year],

      WeekDay(TempDate) as [$(varCalendarNameSpaces) Week-Day]

      Resident TempCalendar

      Order By TempDate ASC;

      Drop Table TempCalendar;

       

      NEXT;

       

      Drop Table CalendarNames;