Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Folks ,
I'm trying a simple logic for calculating no. of days , but some how it's not working .
See below:
LOAD Sales,
( Date(Today(),'MM-DD-YYYY') - Date(Sales,'MM-DD-YYYY')) as NoOfDays
FROM
(ooxml, embedded labels, table is Sheet5);
Kindly help here.
Thanks,
AS
Try below in your script
Interval(Today()-Date(Date#(Sales,'MMM, DD YYYY')),'d') as NoOfDays
Try below in your script
Interval(Today()-Date(Date#(Sales,'MMM, DD YYYY')),'d') as NoOfDays
Use interval function
Ex:-
Interval( Date(Today(),'MM-DD-YYYY') - Date(Date,'MM-DD-YYYY'),'D' ) as NoOfDays,
And
LOAD Sales,
Interval( Date(Today(),'MM-DD-YYYY') - Date(Sales,'MM-DD-YYYY'),'D' ) as NoOfDays
FROM
(ooxml, embedded labels, table is Sheet5)
Regards
Anand
Thanks Manish I was doing the same also but looks like I'm not getting numbers because of sales format 'MMM, DD YYYY' .
Hi,
Try this expression
New:
LOAD
Sales,
Date(Date#(Sales,'MMM, DD YYYY'),'MM-DD-YYYY') as NewDate,
Interval(Date(Today(),'MM-DD-YYYY') - Date(Date#(Sales,'MMM, DD YYYY'),'MM-DD-YYYY'),'d') as NoOfDays
FROM
(ooxml, embedded labels, table is Sheet5)
Regards
Anand
In QlikView, you have to be cautious while handling Dates.... You need to covert the dates before using them in UI end.
If you have below in your script
SET DateFormat='DD/MM/YYYY';
and Dates in your datamodel is M/D/YYYY, you need to match the format as below
Date(Date#(YourDateDate,'M/D/YYYY')) as YourDateField
Now you can use YourDateField for numeric ordering...
Hope this will make clear
Yes I do agree , Actually I was doing the same , was sure something wrong with format but sometimes you do silly things.
Anyways thanks !
AS
Did you try with this statement
Interval(Date(Today(),'MM-DD-YYYY') - Date(Date#(Sales,'MMM, DD YYYY'),'MM-DD-YYYY'),'d') as NoOfDays
Regards
Anand
Yes Anand working fine .
Thanks,
AS