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?
Peter,
Sorry, that doesn't quite work.
it's not just a years worth of month end it also includes the year, so I go back a little over 5 years, I want to keep the number of months as is. I updated with the following
MonthEnd(AddMonths(YearStart(today()),-60+RecNo()-1)) as Date
AutoGenerate $(vNumberOfMonths);
I have to have the date after MonthEnd otherwise I keep ending up with this. as my Fiscal Date.
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?
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.
Similar happens when I choose March 2013, everything gets 0'd out. Why would that be?
