1 Reply Latest reply: Aug 22, 2018 3:11 PM by vamsee duggirala RSS

    Master Calendar - Quarter Year

    surya j

      Hi

       

      I am using the below mentioned script to create a master calendar. Below mentioned are my problem statements

       

      1. The current  QuarterYear should be FY19 Q2 but its showing FY19 Q3 also which logically wrong. How to fix this issue?

       

      2. I want to re frame my QuarterYear format from FYQ2-2018-2019 to FYQ2-2019. Because my model has a data for 2016 but its not showing 2016 option due to the format of my QuarterYear. How to fix this too?

       


      SET vFiscalYearStartMonth = 4;


      LET vStartDate = Num(YearStart(Today(), -1));


      LET vEndDate = Num(YearEnd(Today()));


       


      FiscalCalendar:


      LOAD


      *,


      Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter


      'FY' & Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) & '-' &

      YearName(Date, 0, $(vFiscalYearStartMonth))  as QuarterYear,  // Fiscal YearQuarter


      Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name


      LOAD


      *,


      Year(Date) AS Year, // Standard Calendar Year


      Month(Date) AS Month, // Standard Calendar Month


      Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name


      Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter


      Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month


      YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year


      LOAD


      Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,


      RangeSum(Peek('RowNum'), 1) AS RowNum


      AutoGenerate vEndDate - vStartDate + 1;


       


        • Re: Master Calendar - Quarter Year
          vamsee duggirala

          Hello,

           

          1. The current  QuarterYear should be FY19 Q2 but its showing FY19 Q3 also which logically wrong. How to fix this issue?


          In your expression for Quarters Use Floor instead of Ceil


           

          2. I want to re frame my QuarterYear format from FYQ2-2018-2019 to FYQ2-2019. Because my model has a data for 2016 butitsnot showing 2016 option due to the format of my QuarterYear. How to fix this too?


          I didn't your question right. For 2016 data, use 2016 Jan 1 as your start date

          LET vStartDate = Num(Floor(YearStart(Today(), -2)));

           

          And then to remove additional dates left join the calendar to your data model.