15 Replies Latest reply: Dec 21, 2017 11:36 AM by Chris Hopkins RSS

    Help creating a new field in calendar

    Chris Hopkins

      Hi All,

       

      i am wondering if someone can help with a new field in a calendar.

      basically i want to create a field  which flags the first Monday of July in each fiscal year.

      so current Fiscal year (2018), i want a flag of 0 set against 03/07/2017

      Last Fiscal Year (2017) Flag of -1 against 04/07/2016

      Previous Year (2016) Flag of -2 against 06/07/2015

      etc

       

      can this be achieved easily, i know i can hard code it but i would prefer something automatic so its done as the fiscal calendar gets extended.

       

      any help would be appreciated.

       

      sample attached.

        • Re: Help creating a new field in calendar
          Felip Drechsler

          Hi Chris,


          Could you try the below code please:

           

          Dates:

          LOAD CALENDAR_DDMMYYYY,

              CALENDAR_YEAR,

              CALENDAR_MONTH,

              CALENDAR_DAY,

              CALENDAR_WEEK_NO,

              WEEKDAY,

              FISCAL_PERIOD,

              FISCAL_YEAR,

              FISCAL_PERIOD_NO,

              FISCAL_WEEK_NO,

              CALENDAR_MONTH_TEXT

          FROM

          DATES.qvd

          (qvd);

           

          tmp:

          Load

          CALENDAR_YEAR,

          CALENDAR_MONTH,

          WEEKDAY,

          min(CALENDAR_DAY) as [Flag First Monday]

          Resident Dates

          where CALENDAR_MONTH = 7 and WEEKDAY = 'Monday'

          Group by CALENDAR_YEAR, CALENDAR_MONTH,WEEKDAY;

           

          left join (Dates)

          Load

          CALENDAR_YEAR,

          CALENDAR_MONTH,

          WEEKDAY,

          1 as [Flag First Monday]

          Resident tmp;

           

          drop table tmp;

          • Re: Help creating a new field in calendar
            Sunny Talwar

            May be try this

             

            Dates:

            LOAD CALENDAR_DDMMYYYY,

            If(Month(CALENDAR_DDMMYYYY) = 7 and WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8, Year(Today()) - Year(CALENDAR_DDMMYYYY)) as Flag,

                CALENDAR_YEAR,

                CALENDAR_MONTH,

                CALENDAR_DAY,

                CALENDAR_WEEK_NO,

                WEEKDAY,

                FISCAL_PERIOD,

                FISCAL_YEAR,

                FISCAL_PERIOD_NO,

                FISCAL_WEEK_NO,

                CALENDAR_MONTH_TEXT

            FROM DATES.qvd (qvd);

            • Re: Help creating a new field in calendar
              Marcus Sommer

              I would use the following approach directly within the master-calendar generation:

               

              ...

              if(month(Date) = 7 and weekday(Date) = 0 and day(Date) < 8, 1 * year(Date) - 2018, null()) as Flag

              ...

               

              whereby I would not really prefer to use null() as the else-value else I would use 0 instead and starting the flag-values by 1.

               

              - Marcus

              • Re: Help creating a new field in calendar
                Luis Madriz

                I just did it in Excel to confirm first, but you guys are too quick. I got the same as Sunny, it just needs changing the result around

                 

                Cheers

                 

                Luis

                 

                If(Month(CALENDAR_DDMMYYYY) = 7 and WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8, Year(CALENDAR_DDMMYYYY) - Year(Today())) as Flag,

                  • Re: Help creating a new field in calendar
                    Sunny Talwar

                    You are right, if -1 is needed, just need the change the order of subtraction

                      • Re: Help creating a new field in calendar
                        Chris Hopkins

                        Hi all, thanks for your help, attached is another sample, i have it working so far, so i have the correct flags against the first Monday of July for each year in the calendar - thanks very much!

                        my goal here is to put a flag in the table for each of the dates stating which year it is from (0, -1, -2 etc).

                        so current YTD is 03/07/2017 - vMaxEndDate - which is 09/10/2017.

                        so i want a 0 against each of the dates in that period.

                        and the same for last year, the flag of -1 should be against  04/07/2016-10/10/2016 - note that this needs to be the Monday AFTER the variable date above (if the  variable date (for the year in question) is not a Monday) - we are comparing like for like, i.e. mon - mon

                         

                        does that make sense?

                          • Re: Help creating a new field in calendar
                            Sunny Talwar

                            Is vMaxEndDate a variable which defines your Fiscal Year End Date? Does this change from year to year? Also, you want 0 for 03/07/2017 till 09/10/2017 and -1 for 04/07/2016 till 10/10/2016? But how is this YTD? I am a little confused?

                              • Re: Help creating a new field in calendar
                                Chris Hopkins

                                Hi Sunny,

                                 

                                the vMaxEndDate is the variable that you helped with a few days ago, so its the date that we have data for all customers. it can change anytime - its not the year end.

                                what i mean by last  year YTD is the same period last year, so this year would be..

                                 

                                Mon 03/07/2017 - Mon 09/10/2017

                                last year would be

                                Mon 04/07/2016 - Mon 10/10/2016

                                so last year is exactly the same period as this year, which then allows us to report like for like.

                                 

                                obviosuly then the variable will change as needed, so it could be theat next week we receive data for all customers up until 18/12/2017. that then means that the vMaxEndDate would be 18/12/2017 and the dates needed to compare this yr to last yr would be

                                 

                                Mon 03/07/2017 - Mon 18/12/2017

                                last year would be

                                Mon 04/07/2016 - Mon 19/12/2016

                                does that make sense?

                                • Re: Help creating a new field in calendar
                                  Chris Hopkins

                                  also, the calc needed to work out the vMaxEndDate for previous years can be ignored, i have just realised that i have variables for these that i can re use.

                                  so its just getting help defining the start dates for each year.

                                  so you have helped getting the flags set against each year

                                  0 = current

                                  -1 = last year

                                  -2 = previosu yeat

                                  etc

                                   

                                  what i need is to get an if statement written to pick up all dates >= the date that has the flag of 0 <= vMaxEndDate and set this flag as 0.

                                  e.g.

                                   

                                  If(Date>=date that has flag of 0 and Date<='$(vMaxEndDate)',0,

                                       If(Date>=date that has flag of -1 and Date<='$(vMaxEndDateLY)',-1,


                                  etc


                                  im unsure how to get the 'date that has flag of 0' part

                                • Re: Help creating a new field in calendar
                                  Marcus Sommer

                                  Within the second half of this: How to use - Master-Calendar and Date-Values are various links how to create flags for various measures. They are mostly not designed for fiscal years but I think the logics could be adapted - especially if the fiscal/calendar dates are just regarded as an offset of the other.

                                   

                                  - Marcus

                            • Re: Help creating a new field in calendar
                              Sunny Talwar

                              May be this

                               

                              LET vMaxEndDate = '09/10/2017';


                              Dates:

                              LOAD CALENDAR_DDMMYYYY,

                                  CALENDAR_YEAR,

                                  CALENDAR_MONTH,

                                  CALENDAR_DAY,

                                  CALENDAR_WEEK_NO,

                                  WEEKDAY,

                                  FISCAL_PERIOD,

                                  FISCAL_YEAR,

                                  FISCAL_PERIOD_NO,

                                  FISCAL_WEEK_NO,

                                  AutoNumber(FISCAL_WEEK_NO, 'Overall') as WeekNum,

                                  CALENDAR_MONTH_TEXT

                              FROM DATES.qvd (qvd);


                              Left Join (Dates)

                              LOAD Date(CALENDAR_DDMMYYYY) as YearStartDateMonday,

                              FISCAL_YEAR

                              Resident Dates

                              Where WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8 and Month(CALENDAR_DDMMYYYY) = 7;


                              Max:

                              LOAD Max(WeekNum) as MaxWeekNum

                              Resident Dates

                              Where CALENDAR_DDMMYYYY <= '$(vMaxEndDate)';


                              LET vMaxWeekNum = Peek('MaxWeekNum');

                              LET vLoop = Floor($(vMaxWeekNum)/53);

                              DROP Table Max;


                              FOR i = 0 to $(vLoop);

                               

                              TRACE $(vLoop);

                              LET vCalc = $(vMaxWeekNum) - ($(i)*53);

                              TRACE $(vCalc);


                              Table:

                              LOAD Date(Min(CALENDAR_DDMMYYYY)) as MinDate,

                              FISCAL_YEAR

                              Resident Dates

                              Where WeekNum = $(vCalc)

                              Group By FISCAL_YEAR;

                               

                              NEXT i;


                              Left Join(Dates)

                              LOAD *

                              Resident Table;


                              DROP Table Table;


                              FinalDates:

                              LOAD *,

                              If(Month(CALENDAR_DDMMYYYY) >= 7 and Day(CALENDAR_DDMMYYYY) >= Day(YearStartDateMonday) and CALENDAR_DDMMYYYY <= MinDate, Year(YearStart(CALENDAR_DDMMYYYY, 0, 7)) - Year(YearStart(Today(), 0, 7))) as Flag

                              Resident Dates;


                              DROP Table Dates;

                                • Re: Help creating a new field in calendar
                                  Chris Hopkins

                                  Hi Sunny,

                                   

                                  thanks for this.

                                  i see a problem though.

                                  when selecting fiscal year 2018, i should have 0 against all dates from 03/07/2017-09/10/2017

                                  but i am not getting a 0 on any of the 01 or 02 of each month. so its only flagging from 03 onwards.

                                  its the same for the rest of the years too.

                                   

                                  Capture.JPG

                                  is this easily fixed?

                                    • Re: Help creating a new field in calendar
                                      Sunny Talwar

                                      My bad, try this

                                       

                                      LET vMaxEndDate = '09/10/2017';


                                      Dates:

                                      LOAD CALENDAR_DDMMYYYY,

                                          CALENDAR_YEAR,

                                          CALENDAR_MONTH,

                                          CALENDAR_DAY,

                                          CALENDAR_WEEK_NO,

                                          WEEKDAY,

                                          FISCAL_PERIOD,

                                          FISCAL_YEAR,

                                          FISCAL_PERIOD_NO,

                                          FISCAL_WEEK_NO,

                                          AutoNumber(FISCAL_WEEK_NO, 'Overall') as WeekNum,

                                          CALENDAR_MONTH_TEXT

                                      FROM DATES.qvd (qvd);


                                      Left Join (Dates)

                                      LOAD Date(CALENDAR_DDMMYYYY) as YearStartDateMonday,

                                      FISCAL_YEAR

                                      Resident Dates

                                      Where WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8 and Month(CALENDAR_DDMMYYYY) = 7;


                                      Max:

                                      LOAD Max(WeekNum) as MaxWeekNum

                                      Resident Dates

                                      Where CALENDAR_DDMMYYYY <= '$(vMaxEndDate)';


                                      LET vMaxWeekNum = Peek('MaxWeekNum');

                                      LET vLoop = Floor($(vMaxWeekNum)/53);

                                      DROP Table Max;


                                      FOR i = 0 to $(vLoop);

                                       

                                      TRACE $(vLoop);

                                      LET vCalc = $(vMaxWeekNum) - ($(i)*53);

                                      TRACE $(vCalc);


                                      Table:

                                      LOAD Date(Min(CALENDAR_DDMMYYYY)) as MinDate,

                                      FISCAL_YEAR

                                      Resident Dates

                                      Where WeekNum = $(vCalc)

                                      Group By FISCAL_YEAR;

                                       

                                      NEXT i;


                                      Left Join(Dates)

                                      LOAD *

                                      Resident Table;


                                      DROP Table Table;


                                      FinalDates:

                                      LOAD *,

                                      If(Month(CALENDAR_DDMMYYYY) >= 7 and CALENDAR_DDMMYYYY >= YearStartDateMonday and CALENDAR_DDMMYYYY <= MinDate, Year(YearStart(CALENDAR_DDMMYYYY, 0, 7)) - Year(YearStart(Today(), 0, 7))) as Flag

                                      Resident Dates;


                                      DROP Table Dates;