How to Select Dates which have aged over 2 days

[ivanyang25]

Dear Qlik Sense Experts

I am new to Qlik Sense and I am a front end user

Now I have a form as below and I want to add one filter to select the ones which have aged over 2 days

Let’s say today is 2017/7/29, then I need to exclude 2017/7/27 & 2017/7/28

Then if I refresh data tomorrow, I need to exclude 2017/7/28 & 2017/7/29

May I know how can I achieve it by writing expression in Dimension from front end?

Thank you for your advice in advance

[vinieme12]

Try below

=sum({<[Order Date] = {"<$(=DATE(today()-2,'M/DD/YYYY'))"}>} Qty)

Also read

Dates in Set Analysis

[ivanyang25]

Vineeth

Thank you so much for your prompt reply

I try both of below expression but none of them works

sum({<[Order Date] = {"<$(=DATE(today()-2,'M/DD/YYYY'))"}>} Qty)

sum({<[Order Date] = {"<$(=DATE(today()-2,'M/DD/YYYY'))>"}>} Qty)

Do you have idea which part is wrong?

On the other hand, if I want to put a specific date on it, say dates which are 2 days before '2017/7/10', how should I write the expression?

Thanks for your advise in advance

[antoniotiman · Accepted solution]

Hi Ivan,

You need to set DateFormat in Script accordin Your Source File (MM/DD/YYYY)

SET DateFormat='DD/MM/YYYY';

See Attachment;

Regards,

Antonio

[ivanyang25]

Antonio

Thank you so much for your reply. I have 2 following questions:

1. I didn't find 'MM/DD/YYYY' in your below expression neither can I find it in variate, may I know where did you set it? From back end side or from raw data?

=sum({<[Order Date] = {"<$(=DATE(today()-2))"}>} Qty)

2. If I want a put a specific date in this expression, how can I do it?

e.g. =sum({<[Order Date] = {"<$(=DATE('2017/7/15'-2))"}>} Qty) ?

Thank you for your advise in advance

Ivan

[vinieme12]

What exactly is your date format for field [Order Date]?? 

Is it DD/MM/YYYY or MM/DD/YYYY ? whatever it is just specify the same format in the expression

sum({<[Order Date] = {"<$(=DATE(today()-2,'DD/MM/YYYY'))"}>} Qty)

Modify the date format in the above expression

[antoniotiman]

1) You need to set DateFormat in Script accordin Your Source File (MM/DD/YYYY)

SET DateFormat='DD/MM/YYYY';

2) If Your DateFormat in script is setted to DD/MM/YYYY, then You need Date# to specify format

sum({<[Order Date] = {"<$(=DATE(Date#('2017/7/15','YYYY/M/D')-2))"}>} Qty)

[Anonymous]

Hello Ivan,

The Date format you have used in your expression is not correct. Please go to load load editor and use the same format. Date format should be same as in the data load editor.

Best Regards!

[ivanyang25]

Vineeth

Thank you for your reply

Its little bit weird that it works when I exclude 'DD/MM/YYYY'

sum({<[Order Date] = {"<$(=DATE(today()-2))"}>} Qty)

It also works when I used 'YYYY/MM/DD'

sum({<[Order Date] = {"<$(=DATE(today()-2,'YYYY/MM/DD'))"}>} Qty)

But it doesn't work when I add it in the expression

sum({<[Order Date] = {"<$(=DATE(today()-2,'DD/MM/YYYY'))"}>} Qty)

So I assume the correct data format should be YYYY/MM/DD

Ivan

[ivanyang25]

Amalesh

Thank you for your guidance

Ivan