Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
LET vDateMin = Num(Date(Floor(Today()- 390)));
This works but hard codes the fact that I want (-13 months).
390 is close enough but not accurate! Is there a way I can ju8st take away 13 months?
Jo
May be this? Check Date format
LET vDateMin = AddMonths(Today(), -13);
but ... I want the number to be in days ... for the calendar?
Remove floor and check whether you are getting accuracy date or not?
this is my calendar ... basically i only want 13 months of data - so that January last year can always be compared to January this year. Off course I then want Financial years which in Australia start July 1 to Jun 30 of the following year.
Jo
//Let vDate =Date(Floor(Today()),'YYYY-MM-DD');
Let vDate =Date(Floor(Today()));
//LET vDateMax = Num(MakeDate($(vDate)));
LET vDateMax = Date(Floor(Today()));
//LET vDateMin = Date(Floor(Today()- 390),'YYYY-MM-DD');
LET vDateMin = Num(Date(Floor(Today()- 390)));
//Load All Dates Between Years - Update For Dataset LET vDateMin = Num(MakeDate(2013,07,01));
//// LET vDateMax = Floor(today()); //LET vDateMax = Num(MakeDate(2017,09,01));
//Update for Current Financial Year
//SET vfinYear = '1519';
//Let vLastfinYear = (Num(left($(vfinYear), 2))-1) & (Num(right($(vfinYear), 2))-1);
Fin_Dates_Temp:
LOAD * INLINE
[
MonthNm, FinMonthNm, Quarter,JoMonthNm
Jul, 01, 1,07
Aug, 02, 1,08
Sep, 03, 1,09
Oct, 04, 2,10
Nov, 05, 2,11
Dec, 06, 2,12
Jan, 07, 3,01
Feb, 08, 3,02
Mar, 09, 3,03
Apr, 10, 4,04
May, 11, 4,05
Jun, 12, 4,06
];
Holidays:
Load Holiday as DateFull, Holiday inline
[Holiday
8/06/2015
2/10/2015
3/11/2015
25/12/2015
28/12/2015
1/01/2016
26/01/2016
14/03/2016
25/03/2016
26/03/2016
27/03/2016
28/03/2016
25/04/2016
13/6/2016
30/9/2016
01/11/2016
25/12/2016
26/12/2016
27/12/2016
26/1/2017
13/3/2017
14/4/2017
15/4/2017
16/4/2017
17/4/2017
25/4/2017
];
LET vDateToday = Num(Today());
TempCalendar:
LOAD
Date($(vDateMin) + RowNo() - 1) as DateFull,
Year(Date($(vDateMin) + RowNo() - 1)) as Date_YearNum,
Monthname(Date($(vDateMin) + RowNo() - 1)) as Date_MonthNameTemp,
Num(Month(Date($(vDateMin) + RowNo() - 1))) as Date_MonthNum,
Num(Day(Date($(vDateMin) + RowNo() - 1))) as Date_DayNum,
WeekDay(Date($(vDateMin) + RowNo() - 1)) as Date_DayName,
WeekDay(Date($(vDateMin) + RowNo() - 1)) as ISOWeekDay,
WeekEnd(Date($(vDateMin) + RowNo() - 1)) as Date_WeekEnding,
WeekName(Date($(vDateMin) + RowNo() - 1)) as DateWeekName,
WeekStart(Date($(vDateMin) + RowNo() - 1)) as DateWeekStart
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1 <= num(today());
Left Join(TempCalendar)
LOAD
num(month(date#(MonthNm,'MMM'))) as Date_MonthNameTemp,
FinMonthNm as Date_FinancialMonthNum,
Quarter as Date_Quarter,
JoMonthNm as jojoMonthNm
Resident Fin_Dates_Temp;
Calendar:
LOAD *,
if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,
Date_YearNum & '-' & (Date_YearNum+1),
(Date_YearNum-1) & '-' & Date_YearNum) as Date_FinancialYearName,
if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,
Date_YearNum & '/' & Mid((Date_YearNum+1),3,2),
(Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_Fin,
if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,
Mid(Date_YearNum,3,2) & '/' & Mid((Date_YearNum+1),3,2),
Mid((Date_YearNum-1),3,2) & '/' & Mid(Date_YearNum,3,2)) as Date_FinShrt2,
'Q' & Date_Quarter & '_' & if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,
Date_YearNum & '/' & Mid((Date_YearNum+1),3,2),
(Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_QuartFinYear,
'Q' & Date_Quarter as DateQuarter,
Dual(Date_MonthNameTemp, Date_FinancialMonthNum) as Date_Fin_Month,
Exists(Holiday,DateFull) as IsHoliday,
if(ISOWeekDay>=5,'View Saturdays only','Remove Saturdays') as IsWeekEnd
Resident TempCalendar;
DROP Tables TempCalendar, Fin_Dates_Temp;
TAG Field Date_FinancialYearShrt2 With '$hidden';
You marked as Assumed Answered that means you got solution,
sorry I the mouse ran away from me!
NP, Here is that date is not correct?
20 August 2016 is the date (today - 390)
14/09/2017 | 42992 |
20/08/2016 | 42602 |
That is because, You are using Num() over variable? You can remove that and then try
LET vDateMin = Date(Today()- 390);