12 Replies Latest reply: Apr 6, 2017 6:22 PM by PAUL YEO RSS

    How to Create calendar measure

    PAUL YEO

      Hi All

       

      I have this QVF file , which i like to learn how to Create calendar measure , Hope some one can advise me how to do this ? As i try i keep get error msg " No data field detected....

       

      What i did is ( After view the you tube demo ) :-

       

      Step 1 I click on Field icon , which is lower of 4 icon.

      Step 2 I go to search box , enter " sales "

      Step 3 I click use mouse right click on " sales " Create calendar measure

      Step 4 I see the error msg came out " No data field detected....

       

      Paul Yeo

        • Re: How to Create calendar measure
          PAUL YEO

          Any one can help me ?

            • Re: How to Create calendar measure
              Michael Tarallo

              Hi Paul - I see you have opened the thread, I missed this.


              Check this video out and let me know, please provide detailed steps if this does not work for you:

               

              Creating calendar measures - Qlik Sense - YouTube

               

              When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

               

              Regards,

               

              Michael Tarallo (@mtarallo) | Twitter

              Qlik

                • Re: How to Create calendar measure
                  PAUL YEO

                  Hi Michael

                   

                  I have view the video , and I understand what I should do.

                   

                  What I do is I using the Qlik Sense Demo App , which I know it should be working , but the fact is when I click on the field icon and right click and select create master calendar , the preview of calendar measure does not appear.

                   

                  Hope you can use the QS existing demo QVF and show me how you can create a calendar measure . So I am able to know where I go wrong ?

                   

                  Paul Yeo

                  DIrector

                  TDS Technology (S) P/L

                  Whatsapp +65 9326 1804

                  www.tdstech.com<http://www.tdstech.com>

                    • Re: How to Create calendar measure
                      Michael Tarallo

                      Hi Paul - can you please provide me some screen-shots of what you are seeing? You should be able to copy and past them into the discussion box where you type the reply - you can also attach them (.jpg / .png) as image files.

                       

                      If you choose to attach the screen captures, make sure you put the community discussion editor in "Use advance editor" mode (top right)  - that way the attach link will appear in the bottom right.

                       

                        • Re: How to Create calendar measure
                          PAUL YEO

                          Hi Michael

                           

                          Step 1 , i use QS demo app , sales discovery

                           

                          SD1.png

                          Step 2 , I open the Sheet 1, Performance Dishboard. and i click Edit and Click on Field icon.

                          SD2.png

                          Step 3 , I search for field name "Sales Amount" and i right click Sales Amount.

                          SD3.png

                          Step 4 I click on Create Calendar Measure. and i stuck here. i cannot create mast items from here.

                          SD4.png

                           

                          Hope you can tell me where i go wrong ?

                           

                          Paul Yeo

                        • Re: How to Create calendar measure
                          Michael Tarallo

                          Ok are you getting this error - I just tried your app:

                           

                           

                          You need to have a derived date field (date and associated dimensions) - this is usually interpreted and created by the data manager when you load your tables from source (which automatically inserts the declare and derive statements in the script).

                           

                          I see you are doing a binary load as well - since those fields are not derived yet, and it is not being loaded by the data manager, it will not know to create the auto-calendar.

                           

                          You need to have a date field like this in the source data in order for the data manager to create the auto-calendar script.

                           

                           

                          Then the Create Calendar Measures will show:

                           

                           

                          See my attached sample .qvf

                           

                          If using Qlik Sense Desktop please copy .qvf file to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh Qlik Sense Desktop with F5. If using Qlik Sense Enterprise Server please import .qvf into your apps using the QMC - Qlik Management Console.

                           

                          I also attached the source data so you can see how it takes the OrderDate field and breaks it up into date dimensions. Under the covers it is generating this script:

                           

                          If you want to use your binary load - I WOULD ASSUME you should be able to add this after the load using the date field name you have in your binary loaded data - and replace OrderDate in the syntax below with your date field name.

                           

                          DERIVE FIELDS FROM FIELDS [OrderDate] USING [autoCalendar] ;


                          Here is the complete generated script:

                           

                          [autoCalendar]: 
                            DECLARE FIELD DEFINITION Tagged ('$date')
                          FIELDS
                            Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
                            Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
                            Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
                            Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
                            Month($1) AS [Month] Tagged ('$month', '$cyclic'),
                            Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
                            Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
                            Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
                            Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
                            Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
                            If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
                            Year(Today())-Year($1) AS [YearsAgo] ,
                            If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
                            4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
                            Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
                            If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
                            12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
                            Month(Today())-Month($1) AS [MonthRelNo] ,
                            If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
                            (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
                            Week(Today())-Week($1) AS [WeekRelNo] ;
                          
                          DERIVE FIELDS FROM FIELDS [OrderDate] USING [autoCalendar] ;
                          

                           

                           

                          When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

                           

                          Regards,

                           

                          Michael Tarallo (@mtarallo) | Twitter

                          Qlik

                            • Re: How to Create calendar measure
                              PAUL YEO

                              Hi Michael

                               

                              I try using your Demo App.QVF , I am able to get the preview of measure , as below :-

                               

                               

                               

                               

                              SD_F.png

                              So my issue because of the date field , which i need to create.

                               

                              As refer to below pic , it is only those field which have Calendar 12 icon then can use for create calendar ?

                               

                              SD8.png

                               

                               

                               

                               

                              Paul

                              • Re: How to Create calendar measure
                                PAUL YEO

                                Hi Michael

                                 

                                I follow your instruction , i copy and paste the script you provide me , and i only change the OrderDate , to my master calendar date file.

                                But it still not working,

                                I do notice that the 2 line of my code [autocalendar]: the charater is not turn into blue color , it is gray color , any thing wrong with that ?

                                SD9.png

                                  • Re: How to Create calendar measure
                                    Michael Tarallo

                                    Hi Paul -

                                     

                                    It works for me - see attached apps - App2 - binary loads from App1 - and then uses this code:

                                     

                                    Binary 'lib://DataFolder/app1.qvf';
                                    
                                    
                                        [autoCalendar]:   
                                          DECLARE FIELD DEFINITION Tagged ('$date')  
                                        FIELDS  
                                          Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),  
                                          Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),  
                                          Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),  
                                          Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),  
                                          Month($1) AS [Month] Tagged ('$month', '$cyclic'),  
                                          Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),  
                                          Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),  
                                          Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),  
                                          Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),  
                                          Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),  
                                          If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,  
                                          Year(Today())-Year($1) AS [YearsAgo] ,  
                                          If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,  
                                          4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,  
                                          Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,  
                                          If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,  
                                          12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,  
                                          Month(Today())-Month($1) AS [MonthRelNo] ,  
                                          If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,  
                                          (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,  
                                          Week(Today())-Week($1) AS [WeekRelNo] ;  
                                          
                                        DERIVE FIELDS FROM FIELDS [OrderDate] USING [autoCalendar] ;  
                                    
                                    
                                    
                                    

                                     

                                     

                                    I think the problem might be with the "date" field  name - date is a keyword, I don't think you should use that name, you might need to change it.

                                     

                                    Note my field name grabbing data from app1 is OrderDate

                                      • Re: How to Create calendar measure
                                        PAUL YEO

                                        Hi Michael

                                         

                                        In my QVW file the master calendar script as below , So i should use TempDate instead of date right ? But i just try it should not work. By the way your app1.QVF , i also cannot find the field name with Calendar 12 icon , But for your app2.qvf i found the OrderDate field , have the Calendar 12 icon.

                                         

                                        MasterCalendar:

                                        LOAD TempDate AS date,

                                          D AS link_Date,

                                          D AS Date,

                                          day([TempDate]) as [day],

                                          day(D) AS Day,

                                        //////////////////////////////////// Week

                                        // Week([TempDate]) as Week,

                                          week(D) AS Week,

                                          weekday(D + 2) AS Weekday,

                                          dual(week([TempDate])&'-'&weekyear([TempDate]),weekstart([TempDate])) as YearWeek,

                                        • Re: How to Create calendar measure
                                          PAUL YEO

                                          Hi Michael

                                           

                                          Now it work. I need to go to my load script on sales order table add a field name OrderDate. I can not use the master island calendar Date field , it will not work.

                                           

                                          Thank you very much for you help.

                                           

                                          Paul Yeo

                                          DIrector

                                          TDS Technology (S) P/L

                                          Whatsapp +65 9326 1804

                                          www.tdstech.com<http://www.tdstech.com>