5 Replies Latest reply: Jul 5, 2018 8:07 AM by Cherl Zehnder RSS

    Create a Calendar of just month end dates

    Cherl Zehnder

      I'm working on a calendar of just month end dates. The problem is that the result is a list of month end dates that are duplicated.

      The code looks like this.


      Let vStartDate = Date(AddMonths(YearStart(today()), -60));
      Let vEndDate = Date(MonthEnd(Today()));
      LET vNumberOfDays = vEndDate - vStartDate + 1; 

      Let MonthDiff = Num(((year(vEndDate) * 12) + month(vEndDate)) - (((year(vStartDate) * 12) + month(vStartDate))) + 1);
      LET vNumberOfMonths =   MonthDiff + 1; 

       

      FiscalCalendar: 

      LOAD 

      *, 

      Month(FiscalDate) AS Month, 

      Year(FiscalDate) AS Year, 

      'Q' & Ceil(Month(FiscalDate)/3) AS Quarter, 

      MonthName(FiscalDate) AS MonthYear, 

      'Q' & Ceil(Month(FiscalDate)/3) & '-' & Year(FiscalDate) AS QuarterYear; 

      LOAD Date as FiscalDate;

      LOAD 

      Date(MonthEnd(Date(Date(AddMonths(YearStart(today()), -60))+recno()-1))) as Date

      AutoGenerate $(vNumberOfMonths); 

       

       

      The results look like this, What am I doing wrong?

      Capture.PNG

        • Re: Create a Calendar of just month end dates
          Petter Skjolden

          If you have a closer look at the second to last line of your load script you will find that you have misplaced the +recno()-1 and you can also get rid of the two Date() functions you are using as they serve no purpose. This should work as your last three lines of your load script:

           

          LOAD

            MonthEnd(AddMonths(YearStart(today()),-60+RecNo()-1)) as Date

          AutoGenerate 12;

          • Re: Create a Calendar of just month end dates
            Cherl Zehnder

            I was able to correct the issue and get the calendar I want by adding the Date() function in front of Month end date.

             

            Now I get what I need.  I renamed these fields as valuation date, etc. in order to match my fact table.  That works and the model looks correct.  However, when I try to use the valuation date and valuation month year fields I get odd results.  See below First off you will notice i'm getting 2 of every valuation date, even though I only have one in my qvd calendar file.  Why is that happening?

             

            Capture.PNG

             

             

            The other is that the 2 aren't relating correctly.  If I choose 3/31/2013 my data changes but the valuation month year gets nulled out.

            Capture.PNG

             

             

            Similar happens when I choose March 2013, everything gets 0'd out.  Why would that be?

             

            Capture.PNG