Strainge behaving of Dual() function

[mokitsu61]

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;

[jonathandienst]

>>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.

[mokitsu61]

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;

[jonathandienst]

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

[mokitsu61]

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     //