topic Re: Count the days between two dates in QlikView

Count the days between two dates

Mon, 14 Sep 2015 06:36:33 GMT

Hello,

Can you tell me how can I count the days between two dates ?

I want to create a chart with opened cases and I have to create some dimensions in days.

<7 days 14-14 days 14-30 days 30-90 days.

Similar to this chart.

And I don't have any ideas how to do this. Can you give me a clue ?

Thanks!

Re: Count the days between two dates

Mon, 14 Sep 2015 06:40:04 GMT

Hi,

Can you share your qvw file or sample file.

Re: Count the days between two dates

gautik92 — Mon, 14 Sep 2015 06:41:16 GMT

=fabs(Date(EndDate,'DD/MM/YYYY')) - fabs(Date(StartDate,'DD/MM/YYYY'))

Re: Count the days between two dates

Mon, 14 Sep 2015 06:50:01 GMT

Is not working.

Re: Count the days between two dates

sasiparupudi1 — Mon, 14 Sep 2015 06:50:09 GMT

Use a calculated dimension with an if condition like below

if (Interval(DateField2 - DateField1,'D')<7,'<7 days',

if(Interval(DateField2 - DateField1,'D')>7 and Interval(DateField2 - DateField1,'D')<14,'7-14 days',

if(Interval(DateField2 - DateField1,'D')>14 and Interval(DateField2 - DateField1,'D')<30,'14-30 days',

if(Interval(DateField2 - DateField1,'D')>30 and Interval(DateField2 - DateField1,'D')<90,'30-90 days','>90 Days'))))

hth

Sasi

Re: Count the days between two dates

gautik92 — Mon, 14 Sep 2015 06:57:45 GMT

can you share sample qvw file?

Re: Count the days between two dates

Mon, 14 Sep 2015 07:02:40 GMT

Hi try this:

If date field don't have time stamp

Date(todate)-date(frondate) as date-count

If time stamp first u have to trim the time then use above statement

Re: Count the days between two dates

gautik92 — Mon, 14 Sep 2015 07:08:02 GMT

have a look at jontydkpi reply for the thread

difference between two dates.

it might help you

Re: Count the days between two dates

Mon, 14 Sep 2015 07:15:29 GMT

Thank you ! Very helpful.

Re: Count the days between two dates

Mon, 14 Sep 2015 07:15:45 GMT

Thank you very much !

Re: Count the days between two dates

Thu, 28 Apr 2016 22:32:37 GMT

Thanks you so much!

Re: Count the days between two dates

mjtaft2017 — Tue, 18 Jul 2017 23:09:13 GMT

If you can put this in the load script it would be more efficient than putting this expression in as your dimension in the chart. I'm new to Sense also so I am not sure if you can use the Interval function in your syntax if you put this "IF" in the load script, but you can try it as

@Sasidhar Parupudi mentioned in his post. His response is correct - I just had to do something similar in QlikView and it was mentioned to put it in the load script if possible for better performance.

if (Interval(DateField2 - DateField1,'D')<7,'<7 days',

if(Interval(DateField2 - DateField1,'D')>7 and Interval(DateField2 - DateField1,'D')<14,'7-14 days',

if(Interval(DateField2 - DateField1,'D')>14 and Interval(DateField2 - DateField1,'D')<30,'14-30 days',

if(Interval(DateField2 - DateField1,'D')>30 and Interval(DateField2 - DateField1,'D')<90,'30-90 days','>90 Days')))) AS Interval