Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor III

Cal no. of days from today ???

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

1 Solution

Accepted Solutions

Re: Cal no. of days from today ???

Try below in your script

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

8 Replies

Re: Cal no. of days from today ???

Try below in your script

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

Re: Cal no. of days from today ???

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
Honored Contributor III

Re: Cal no. of days from today ???

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

Re: Cal no. of days from today ???

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

Re: Cal no. of days from today ???

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
Honored Contributor III

Re: Cal no. of days from today ???

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

Re: Cal no. of days from today ???

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
Honored Contributor III

Re: Cal no. of days from today ???

Yes Anand working fine .

Thanks,
AS

Community Browser