Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm struggling with functions related with Date.
Following test script tries to create tables Master Calender(Temp_Cal) and User Calendar(MMYYYY_tets).
I believed [User Calender] returns ...
MMYYY | MMYYY_dual_test
...
01-2019 | 2019
02-2019 | 2019
03-2019 | 2019
...
but [User Calender] preview shows ...
MMYYY | MMYYY_dual_test
...
01-2019 | 2018
02-2019 | 2018
03-2019 | 2018
...
Actually I set dual value as so, but I think table preview shows "Real value".
Does anyone know why Table Preview returns such value?
----------------- Test script -------------------
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';
Set FirstMonthOfYear = 4;
// Master Calendar
Temp_Cal:
LET varDateMin_Num = Num(Date#('01-04-2018', 'DD-MM-YYYY'));
LET varDateMax_Num = Num(Date#('31-03-2019', 'DD-MM-YYYY'));
LOAD
date($(varDateMin_Num) + rowno() - 1) AS Temp_Date
AutoGenerate
$(varDateMax_Num) - $(varDateMin_Num) + 1;
// User Calendar
MMYYYY_test:
LOAD
Date(Temp_Date, 'MM-YYYY') AS MMYYYY,
Dual(Year(Temp_Date), if(Month(Temp_Date)>=4, Year(Temp_Date), Year(Temp_Date)-1)) AS MMYYYY_dual_test
Resident Temp_Cal;
>>Date(Year(Temp_Date), 'MM-YYYY') AS MMYYYY,
What are you trying to do here? This will take the year of Temp_Date as a number (like 2018), and return a date 2018 days after the start of the date epoch (1 = 31 Dec 1900). I am sure that its not the desired result. Perhaps you mean
Date(MonthStart(Temp_Date), 'MM-YYYY') AS MMYYYY,
I assume the line is part of a fiscal year calculation.
There were mistakes in [--- Test sctipt ---].
I hope following new [Test code] tells you what I want to ask.
Thank you.
----------------- Test script -------------------
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';
Set FirstMonthOfYear = 4;
// Master Calendar
Temp_Cal:
LET varDateMin_Num = Num(Date#('01-04-2018', 'DD-MM-YYYY'));
LET varDateMax_Num = Num(Date#('31-03-2019', 'DD-MM-YYYY'));
LOAD
date($(varDateMin_Num) + rowno() - 1) AS Temp_Date
AutoGenerate
$(varDateMax_Num) - $(varDateMin_Num) + 1;
// User Calendar
MMYYYY_test:
LOAD
// Date(Year(Temp_Date), 'MM-YYYY') AS MMYYYY, // Mistake
Date(Temp_Date, 'MM-YYYY') AS MMYYYY,
Dual(Year(Temp_Date), if(Month(Temp_Date)>=4, Year(Temp_Date), Year(Temp_Date)-1)) AS MMYYYY_dual_test
Resident Temp_Cal;
That makes a little more sense. But ths line
>>Date(Temp_Date, 'MM-YYYY') AS MMYYYY,
needs to be modified
>>Date(MonthStart(Temp_Date), 'MM-YYYY') AS MMYYYY,
Otherwise you will land up with multiple records that look the same but have different underlying values.
I am still not clear how you want to do the fiscal periods. Search here for 'fiscal calendar', 'financial calendar' etc
My question is ...
> Dual(Year(Temp_Date), if(Month(Temp_Date)>=4, Year(Temp_Date), Year(Temp_Date)-1)) AS MMYYYY_dual_test
>
...
MMYYY | MMYYY_dual_test
01-2019 | 2018 // Value of [MMYYY_dual_test] should be [2019] because I set [MMYYY_dual_test] as Dual(2019, 2018)
02-2019 | 2018 //
03-2019 | 2018 //