9 Replies Latest reply: May 29, 2014 2:07 AM by Janet Halliday RSS

    Creating FY from MM/YYYY without using Calendar

      Hello All - new to QlikView, but not to Access and Excel;

       

      I have 3 columns of data loaded from one field as follows: (original data is YYYYMMDD)

           Date(Date#([Date Lead Submitted],'YYYYMMDD'),'MM/DD/YYYY')as DateLeadSubmitted,                       (Yield 11/09/2010)

           Date(Date#([Date Lead Submitted],'YYYYMMDD'),'MM') as MonthSubmitted,                                          (Yield 11)

           Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY') as YearSubmitted,                                         (Yield 2010)

       

      Now I need to create a FYSubmitted field which adds 1 to the year when the month is >9; this is what I've been working on, but many variations later, I still get nothing but a dash but oddly enough the script doesn't blow up:                              (Desired 2011)

       

           If (Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY') > 09, Date(Date#([Date Lead Submitted],'YYYYMMDD

           +1'),'YYYY'), Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY')) as FYSubmitted,

       

      any takers?

       

       

      Jan

        • Re: Creating FY from MM/YYYY without using Calendar

          Hi Jan

           

          Check This

           

          If (Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))) > 09,

               Date(MakeDate( Year(Date#([Date Lead Submitted],'YYYYMMDD'))+1,

                                              Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))),

                                              Num(Day(Date#([Date Lead Submitted],'YYYYMMDD')))

                                          )

                          , 'YYYY'),

          Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY')) as FYSubmitted,

            • Re: Creating FY from MM/YYYY without using Calendar

              Thank you!!! One wonders why it needs to be so convoluted…?’’I’m grateful to the “plug and play” coding, thank you, but I pride myself in being able to read formulas fairly easily, but this one throws me for a loop :P

               

              Jan ☺

              • Re: Re: Creating FY from MM/YYYY without using Calendar

                OK, I'm going to push my luck - the FY above works marvelously, and I placed part of that formula into my calendar and can successfully get a correct FY - but now I'm stuck on FQ

                 

                here is an excerpt from my Calendar script: AND IT WORKS THANK YOU MARCOS!!!

                If (Num(Month(Date#([TempDate]))) > 09,

                     Date(MakeDate( Year(Date#([TempDate]))+1,Num(Month(Date#([TempDate]))),

                     Num(Day(Date#([TempDate])))), 'YYYY'),

                     Date(Date#([TempDate]),'YYYY')) as FYYear,

                 

                what I now need is to get the Fiscal Quarter to skew so that 10/1 starts Q1 of the next fiscal year and I think this why everyone uses a calendar:

                (but I can't make the simple code provided HERE to work . I've pasted my calendar code at the bottom of this page in case some need to see it in its entirety. The SET vFM = 10 is on the Main Load Page with all the other variables)

                 

                10/01/2014 EQUALS FY15 Q1

                 

                I've been playing with this:

                    'Q' & ceil((month(TempDate)/3)-3) as FYQuarter,

                but that renders negative numbers and doesn't recycle back to 1

                 

                I have a lookup table that I am currently using for an excel spreadsheet, can I somehow utilize that?:

                FY-QQ-BeginQ-End
                FY12 Q36/1/20126/30/2012
                FY12 Q47/1/20129/30/2012
                FY13 Q110/1/201212/31/2012
                FY13 Q21/1/20133/31/2013
                FY13 Q34/1/20136/30/2013
                FY13 Q47/1/20139/30/2013
                FY14 Q110/1/201312/31/2013
                FY14 Q21/1/20143/31/2014
                FY14 Q34/1/20146/30/2014
                FY14 Q47/1/20149/30/2014
                FY15 Q110/1/201412/31/2014
                FY15 Q21/1/20153/31/2015
                FY15 Q34/1/20156/30/2015
                FY15 Q47/1/20159/30/2015

                 

                ~ ~ ~ MasterCalendarCode ~ ~ ~

                 

                MinMax:

                LOAD

                  Min(DateLeadSubmitted) as MinDate,

                  Max(DateLeadSubmitted) as MaxDate

                RESIDENT FSA_BaseDataSAP;

                 

                 

                LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

                LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

                LET vToday = $(vMaxDate);

                 

                 

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

                TempCal:

                LOAD

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

                 

                 

                AUTOGENERATE

                  $(vMaxDate) - $(vMinDate)+1;

                 

                DROP TABLE MinMax;

                 

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

                MasterCalendar:

                LOAD

                  TempDate as Date,

                // Week(TempDate) as Week,

                  Year(TempDate) as Year,

                  If (Num(Month(Date#([TempDate]))) > 09,

                    Date(MakeDate( Year(Date#([TempDate]))+1,Num(Month(Date#([TempDate]))),

                    Num(Day(Date#([TempDate])))), 'YYYY'),

                    Date(Date#([TempDate]),'YYYY')) as FYYear,

                        Date(monthstart(TempDate),'MM') as Month,

                // Month(TempDate) as Month,

                  Day(TempDate) as Day,

                // Weekday(TempDate) as WeekDay,

                  'Q' & ceil(month(TempDate)/3) as Quarter,

                    'Q' & ceil((month(TempDate)/3)-3) as FYQuarter,

                  Date(monthstart(TempDate),'MM-YYYY') as MonthYear,

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

                  inyeartodate(TempDate,$(vToday),0)*-1 as CurYTDFlag,

                  inyeartodate(TempDate,$(vToday),-1)*-1 as LastYTDFlag

                  RESIDENT TempCal

                  ORDER BY TempDate ASC;

                 

                  DROP TABLE TempCal;

              • Re: Creating FY from MM/YYYY without using Calendar

                Sorry

                 

                Us this version please, the last has an error

                 

                If (Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))) > 9,

                    Date(MakeDate( Year(Date#([Date Lead Submitted],'YYYYMMDD'))+1,

                                    Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))),

                                    Num(Day(Date#([Date Lead Submitted],'YYYYMMDD')))

                                  )

                        , 'YYYY')

                  ,Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY')

                  ) as FYSubmitted,

                • Re: Creating FY from MM/YYYY without using Calendar
                  jagan mohan rao appala

                  Hi,

                   

                  Try like this

                   

                  LOAD

                  *,

                  Year(YearStart(Date(Date#([Date Lead Submitted], 'YYYYMMDD')), 0, 10)) + 1 AS Year

                  From DataSource;

                   

                  Regards,

                  Jagan.

                  • Re: Creating FY from MM/YYYY without using Calendar

                    Janet,

                     

                    At most custoemr sites,  just like  your current place of work,  people use  Time Dimension Tables  from the database as a better solution  than  writing code to convert Calendar date or Julian Date to Fiscal Date.  For example  at our employer, we have a database table called Time_Dim  which you can join to the said date  field (Lead date...?)  and the time dim table record  may have many columns  called FY, FQ, Fperiod, Week #, Day number, etc., to the point that you can get any specific date field  incldung day names, exclude/include holdiays etc.,

                    that method is 100 times less work and more accurate (because the script examples below have to be done for each date column!)

                     

                    Time Dim table is created one time for subject date range (say 1960 to 2099)  and saved and you dont have to relead that QVD multiple times., Just join on the Date field.  We do this  at Siemens!