Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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');
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');
Thank you @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');
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));
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?
Hi @Amelia_96
Try like below
Where floor(Date#(DRPROPENTRY ,'DDMMMYYYY:hh:mm:ss')) >= Floor($(vBeforeDate));
If not, pls share the sample file.
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');
Thank you @MayilVahanan ! This is working.