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

[Amelia_96]

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!

[MayilVahanan]

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');

[Amelia_96]

Thank you @MayilVahanan 

[Amelia_96]

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');

[MayilVahanan]

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));

[Amelia_96]

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?

[MayilVahanan]

Hi @Amelia_96 

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

If not, pls share the sample file.

[Amelia_96]

Hi @MayilVahanan 

Still got issue.

Here's the sample file. 

Really appreciate you assistance

[MayilVahanan · Accepted solution]

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');

[Amelia_96]

Thank you @MayilVahanan ! This is working.