Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cworkman2015
Contributor III
Contributor III

Function in Script returns different value than function in Text object

Hello,

I'm experiencing something very confusing. I am loading a temporary table with this script:

Temp_Calendar_Range:

Load

Num(Date#(Min([Accrual Period]), 'YYYYMM'))  as MinDate,

Num(Date#(Max([Accrual Period]), 'YYYYMM'))  as MaxDate

Resident [Fact_Accrual];

//Assign the start and end dates to variables

Let vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

Let vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');

However, the MinDate and MaxDate values from the script do not match this same function in a text object.

The table on the right is the Temp_Calendar_Range table. The Blue box is the text object. Note that MinDate for Temp table and the Blue box are completely different.

dateissue.PNG

Has anyone experienced this before?

Thanks,

Channing

2 Replies
settu_periasamy

May be try to convert the Date first, then do the minimum / Maximum.. Like

Temp_Calendar_Range:

Load

Num(Min(Date#([Accrual Period], 'YYYYMM')))  as MinDate,

Num(Max(Date#([Accrual Period], 'YYYYMM')))  as MaxDate

Resident [Fact_Accrual];

Can you tell what is the format of Accrual Period / may be some sample?

cworkman2015
Contributor III
Contributor III
Author

Settu,

That worked. However, now I'm seeing Accrual Periods that appear to be interpreted incorrectly.

Before Calendar Creation

Accrual Period Range: 201601 - 201712 (Date Format YYYYMM)

After Calendar Creation

Accrual Period Range: 201601 - 245204 (Date Format YYYYMM)

I have no clue where these values are coming from.. Maybe the max period is being interpreted  incorrectly. Here's my script.

//////Accrual Fact Table

Temp_Calendar_Range:

Load

Num(Min(Date#([Accrual Period], 'YYYYMM')))   as MinDate,

Num(Max(Date#([Accrual Period], 'YYYYMM')))  as MaxDate

Resident [Fact_Accrual];

//Assign the start and end dates to variables

Let vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

Let vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');

Drop table Temp_Calendar_Range; //cleanup

[Master Calendar Fact Accrual]:

Load Distinct

Year(Temp_Date) * 100 + Month(Temp_Date) as [Accrual Period],

Year(Temp_Date) as [Accrual Year],

Month(Temp_Date) as [Accrual Month],

Date(Temp_Date, 'YYYY-MM') as [Accrual Year - Month],

'Q' & Ceil(Month(Temp_Date) / 3) as [Accrual Quarter]

;

Load Distinct

MonthStart($(vMinDate) + IterNo()-1) as Temp_Date

AutoGenerate(1)

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

//Remove the temporary values

LET vMinDate = null();

LET vMaxDate = null();