3 Replies Latest reply: Sep 18, 2017 4:25 AM by Robert Hutchings RSS

    How to defind current date into Month/Quarter/Fiscal Year?

    Ivan Yang

      Dear QS Experts

       

      I am new to Qlik Sense and I have question regarding to date in Qlik Sense Desktop

       

      In my raw data there is a column named 'POD Date' stores data like '2017/7/20' , '2017/4/3', '2017/6/8' etc

       

      I want to create a dimension in master item to define these date into relative Month/Quarter/Fiscal Year

       

      For month I can use expression like =num (Month([POD Date])) to get the result I want (7,4,6)

       

      How to acheive the same goal for Quarter (i.e Q1,Q2) / Fiscal Year (i.e 1718)?

       

      PS: I want to solve this problem in front end instead of writing script from back end

       

      Thank you for your advise in advance

       

      Ivan

        • Re: How to defind current date into Month/Quarter/Fiscal Year?
          Robert Hutchings

          Hi

           

          I usually use quartername. This gives Jan-Mar 2017 etc. Its a dual so is sortable by number. But other options are quarter end or start date

           

          Fiscal Year I always do in script. In fact all calendars I do in script. And if you use data manager its done automatically using derived fields

           

          Refer a good explanation on this

           

          Fiscal Year

           

          One recent example I did. Just replace DateCanonn and CallInDate with POD Date

           

          Set vFM = 10 ;  // year Oct to Sept  First month of fiscal year

          Calendar:  //inc fiscal years

          //level 5

          Load

          Dual(fYear-1 &'/'& fYear, fYear) as YearFin,          // Dual fiscal year

          Dual(Month, fMonth)                as MonthFin,           // Dual fiscal month for sorting

          *

          ;

          //level 4

          Load

          Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

          Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *

          ;

          Calendar: 

          //level 3

          load

          DateCanon ,

          Month(DateCanon) as Month,

          Year(DateCanon) as Year,

          Day (DateCanon) as Day,

          weekday (DateCanon) as DayMonEtc,

          weekend (DateCanon,0,-2) as WeekEnding  ,

          Week (DateCanon) AS WeekNum,

          Monthstart (DateCanon) as MthYear ;

          //level 2

          Load Date (MinDate+iterno()) as DateCanon While iterno() <= MaxDate - MinDate ;

          //level 1

          Load Min(CallInDate)-1 as MinDate, Max(CallInDate)+30 as MaxDate Resident SCCall ;

            • Re: How to defind current date into Month/Quarter/Fiscal Year?
              Ivan Yang

              Robert

               

              Thank you for the feedback

               

              As I am a front end user thus it is a little bit difficult for me to read below material

              Fiscal Year

               

              Should I add the script in into below button or in Data Load Editior?

              捕获.PNG

               

               

              There are several datas in my raw data (order date/ship date/ POD date etc) and I need POD date for reference to show fiscal year and quarter in sheet 1 and Ship date in sheet 2. Which date in below script example should I replace with ship date/POD date?

               

              On the other hand, how should we work quarter out?

               

              It will be highly appreciated if you could share me a test apps for reference

               

              2.PNG

              Thanks

                • Re: How to defind current date into Month/Quarter/Fiscal Year?
                  Robert Hutchings

                  Try this

                   

                  Put all this script in at the end using data load editor

                   

                  This will create a master calendar for every day from a minimum starting date to an end date

                   

                  And link it to POD Date

                   

                  let me know if it works or not

                   

                  Set vFM = 10 ;  // year Oct to Sept  First month of fiscal year set as appropriate >> required level 4

                   

                  //level 5 reads in level 4 then drops these level 4 fields

                  Calendar:  //inc fiscal years

                  Load

                  Dual(fYear-1 &'/'& fYear, fYear) as YearFin,          // Dual fiscal year

                  Dual(Month, fMonth)                as MonthFin,           // Dual fiscal month for sorting

                  *                                                                              // Picks up fields in level 4

                  ;

                  //level 4 reads in level 3 then drops these level 3 fields

                  Load

                  Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

                  Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

                  *                                                                           // picks up fields in level 3

                  ;

                    //level 3 reads in level 2 then drops these level 2 fields

                  MASTERCalendar:

                  load

                  [POD Date],     ///LINK field

                  Month([POD Date]) as Month,   //PUT IN CALENDAR FIELDS AS REQUIRED

                  Year([POD Date]) as Year,

                  Day ([POD Date]) as Day,

                  Week ([POD Date]) AS WeekNum,   //if required

                  Monthstart ([POD Date]) as MthYear ;  //if required

                  //level 2 reads in level 1 then drops these level 1 fields

                  Load Date (MinDate+iterno()) as [POD Date] While iterno() <= MaxDate - MinDate

                  ;

                  //level 1 //start of load .. must link to another table

                  Load Min([POD Date])-1 as MinDate,

                  Max([POD Date])+30 as MaxDate //change days (30) as required

                  Resident >>>your table Name ;  // link to the appropriate table that contains POD date