Calculated Field with Auto Generated Date Field.

[Sara_3]

Hello ,

My requirement is to get Calculated field "Days Aged within PO DOC and PO ITEM , but getting this field I have to do some steps. I am able to do in excel but not able to do in qlik. 

My data is :

Table A:

LOad *,

PO DOC,

PO ITem,

As of Date

From QVD;

I have PO DOCs and its multiple PO Items with As of Dates.  I selected the PO DOC with PO ITEM = 10 .

Data:

Expected Field "Days Aged" :

Steps to get the Expected Field "Days Aged":

Step 1 : Create Min and Max (As of Days) field within / group by PO DOC and POITem. 

Min (As of Date ) = 09/21/2020   and Min ( As of Date) = 09/25/2023

Step 2: create a auto generated Date field between Min and Max Dates (like auto generated field for the above data between 09/21/2020 - 09/25/2023)

Step 3 : Create Flag 1, Check (if [As of Date] matched/ exists in Auto generated Date field , 'YES', 'NO')

 Flag 1 (column 4)

Step 4: Create Flag 2 - if (As of Date has previous data (As of Date), 'YES','NO') as Flag 2 (Column 5)

Step 5 : Field "Days Aged" (if (both Flags = 'YES' start with '0' and Range SUM (Current (As of Date ) - Previous (As of Date) . (Column 6) 

Step 6 : Expected Field:

Days Aged = 85 with MAX (as of Date)

Thanks in advance .

[Qlik_BA]

Hi Sara,

 Try this. I hope it works for you,

Use DateDiff() to calculate the days elapsed between "Starting from Date" and "As of Date."

• Create a calculated field with these elapsed days.
• Filter the data for PO DOC with PO ITEM = 10 using WHERE.
• Script :

Table:

LOAD *,

DateDiff('days', [Starting from Date], [As of Date]) AS [Days Elapsed]

FROM [your QVD file.qvd]

WHERE [PO ITEM] = 10;