Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
mokitsu61
Contributor

Strainge behaving of Dual() function

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;

4 Replies
MVP
MVP

Re: Strainge behaving of Dual() function

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mokitsu61
Contributor

Re: Strainge behaving of Dual() function

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;

MVP
MVP

Re: Strainge behaving of Dual() function

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mokitsu61
Contributor

Re: Strainge behaving of Dual() function

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