Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
46897435
Contributor
Contributor

How can I use variable with yearstart and max date to filter two years from most recent date in data

I've tried the following variable:

Let StartDate = yearstart(max(Date),-2)

and then called it like this:

LOAD

*

Where Date >= '$(StartDate)'

;

However, this is not working. NOTE: Date is a date field in mm/dd/yyyy format and it is part of database imported from Access

2 Replies
dilipranjith
Partner
Partner

 

 

the max doesnt work in that way

you will need something like below

 

Temp:
Load
    max(OrderDate) as maxDate
Resident yourtable;


Let varMaxDate = yearstart(Peek('maxDate', 0, 'Temp'),-2);

 

SukumarBera
Contributor III
Contributor III

Solution :

This is a two steps process-

  • Determine the maximum date using FieldValue and FieldValueCount functions and store that into a variable.
  • Create another variable to determine the Last to Last year using YearStart function.

Please have a look at my script below . I used an INLINE LOAD with one date field.

Data:
LOAD date#(Date,'MM/DD/YYYY') AS Date Inline
[
Date
09/26/2019
09/25/2019
09/24/2019

];

Tab:
LOAD recno() AS ID,Date(max(FieldValue('Date',RecNo())),'DD-MM-YYYY') AS MaxDate
AutoGenerate FieldValueCount('Date');

LET vMaxDate=PEEK('MaxDate',0,'Tab');
Drop Table Tab;
LET vStartDate=YearStart(vMaxDate,-2);

Finally called it like this:

LOAD

*

Where Date >= '$(StartDate)'

;

 

Thanks & Regards,

Sukumar Bera