topic Re: Filtering data based on current date and same period (before & after) in QlikView

Filtering data based on current date and same period (before & after)

Amelia_96 — Fri, 20 Nov 2020 18:58:42 GMT

Hi, currently i'm doing dashboard that need to be updated everyday (morning).

My start date is 01/11/2020. But i need to have comparison between 'Before' & 'After'. So starting from 01/11/2020 up to current date (today=09/11/2020) will be 'After' (equivalent to 9days), and the same period for 'Before' (equivalent to 9days) will be from 23/10/2020 up to 31/10/2020.

Below are the sample expected output for each day when data is updated:

Does anyone knows how do i write this in QlikView script to get this output when data is updated everyday?

Please help. Thank you in advance!

Re: Filtering data based on current date and same period (before & after)

MayilVahanan — Mon, 09 Nov 2020 11:15:47 GMT

Hi @Amelia_96

Try like below

Let vStartDate = MakeDate(2020, 11,1);

Let vAfterDate = Interval(Today() - vStartDate, 'D');

Let vBeforeDate = vStartDate - vAfterDate;

LOAD Distinct
Date,
If(Date < Date('$(vStartDate)','YYYY-MM-DD'), 'Before', 'After') as Period
FROM
YourSource

where Date >= Date($(vBeforeDate),'YYYY-MM-DD');

Re: Filtering data based on current date and same period (before & after)

Amelia_96 — Wed, 11 Nov 2020 09:49:54 GMT

Thank you @MayilVahanan

Re: Filtering data based on current date and same period (before & after)

Amelia_96 — Fri, 13 Nov 2020 07:24:16 GMT

Hi @MayilVahanan

I tried to use this solution here, but it doesnt give the correct output.

Is there anything wrong with my code?

Let vStartDate = Date#(MakeDate(01,05,2020),'DDMMMYYYY:hh:mm:ss');

Let vLatestDate = Date#(MakeDate(16,09,2020),'DDMMMYYYY:hh:mm:ss');

Let vAfterDate = Interval(vLatestDate - vStartDate, 'D');

Let vBeforeDate = vStartDate - vAfterDate;

D1:
LOAD
DTPROPENTRY,
If(DTPROPENTRY < Date('$(vStartDate)','YYYY-MM-DD'), 'Before', 'After') as PERIOD
FROM source
where Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss') >= Date#('$(vBeforeDate)', 'DDMMMYYYY:hh:mm:ss');

Re: Filtering data based on current date and same period (before & after)

MayilVahanan — Fri, 13 Nov 2020 05:01:23 GMT

Hi @Amelia_96

Can I know the reason for using Date#() instead of date(). And for where condition, try with floor().

Hope DTPROPENTRY field are in date format already.

where Floor(DTPROPENTRY) >= Floor($(vBeforeDate));

Re: Filtering data based on current date and same period (before & after)

Amelia_96 — Fri, 13 Nov 2020 06:31:20 GMT

Actually i'm still a bit confused on the formatting of date. So basically, the field DRPROPENTRY is text and in this form '01Jan2020:00:00:00'. So when i used your date from previous solution, it can't be done because different format. Can you help me to suggest what is the best way on this matter?

Re: Filtering data based on current date and same period (before & after)

MayilVahanan — Fri, 13 Nov 2020 07:00:40 GMT

Hi @Amelia_96

Try like below
Where floor(Date#(DRPROPENTRY ,'DDMMMYYYY:hh:mm:ss')) >= Floor($(vBeforeDate));

If not, pls share the sample file.

Re: Filtering data based on current date and same period (before & after)

Amelia_96 — Fri, 13 Nov 2020 07:17:39 GMT

Hi @MayilVahanan

Still got issue.

Here's the sample file.

Really appreciate you assistance

Re: Filtering data based on current date and same period (before & after)

MayilVahanan — Fri, 13 Nov 2020 07:39:38 GMT

Hi @Amelia_96

Try like below

Let vStartDate = MakeDate(2020, 9,1);

Let vAfterDate = Interval(MakeDate(2020, 9,15) - Floor(vStartDate), 'D');

Let vBeforeDate = vStartDate - vAfterDate;

LOAD Date(Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss')) as DTPROPENTRY,
If(Date(Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss')) < Date('$(vStartDate)','YYYY-MM-DD'), 'Before', 'After') as Period
FROM
[D:\Qlik\personnal\Source.xlsx]
(ooxml, embedded labels, table is Source)
Where Date(Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss')) >= Date($(vBeforeDate),'YYYY-MM-DD');;

You can change the dates below 2 values

Let vStartDate = MakeDate(2020, 9,1);

Let vAfterDate = Interval(MakeDate(2020, 9,15) - Floor(vStartDate), 'D');

Re: Filtering data based on current date and same period (before & after)

Amelia_96 — Fri, 13 Nov 2020 08:55:28 GMT

Thank you @MayilVahanan ! This is working.

Re: Filtering data based on current date and same period (before & after)

Frank_Kipry — Fri, 13 Nov 2020 09:02:40 GMT

I would try

table_new

NoConcatenate LOAD *,

if(Date < Date('01.11.2020'), 'Before', 'After') as Period

Resident table_old;

Drop Table table_old

With that you don't have to change your code, you just load the data from memory again, work with it and then drop the old stuff.

BUT, in general: don't use "Date" as field... since it's also a command, I would say that's bad programming style.