Cal no. of days from today ???

[amit_saini]

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

[MK_QSL · Accepted solution]

Try below in your script

Interval(Today()-Date(Date#(Sales,'MMM, DD YYYY')),'d')  as NoOfDays

[its_anandrjs]

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

[amit_saini]

Thanks Manish I was doing the same also but looks like I'm not getting numbers because of sales format 'MMM, DD YYYY' .

[its_anandrjs]

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

[MK_QSL]

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

[amit_saini]

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

[its_anandrjs]

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

[amit_saini]

Yes Anand working fine .

Thanks,
AS