topic Re: Days difference between column of dates in QlikView

Days difference between column of dates

Anonymous — Mon, 25 Mar 2013 12:35:55 GMT

I have a column of dates in the yyyy-mm-dd format. What I would like to achieve is to add a column (either an expression or a calculated dimension) that calculates the difference in number of days between a date and the previous date, e.g:

Date	Difference_in_days
2013-03-20	-
2013-03-15	5
2013-03-14	1
2013-03-10	4
2013-03-01	9

Any suggestions on how to go about this?

Thanks

Matt

Re: Days difference between column of dates

Gysbert_Wassenaar — Mon, 25 Mar 2013 12:39:03 GMT

You can use the above function in tables and charts: above(Date) - Date. Make sure the dates are real dates and not strings. Use the date# function if necessary: date#(Date, 'YYYY-MM-DD')

Re: Days difference between column of dates

Anonymous — Mon, 25 Mar 2013 13:06:42 GMT

Thanks Gysbert. In SQL my column Date is set as type date so do I still need to set the date# function within Qlikview?

Currently, above(Date)-Date is returning "// Error in calculated dimension".

Re: Days difference between column of dates

Gysbert_Wassenaar — Mon, 25 Mar 2013 13:13:20 GMT

You can't use it as a calculated dimension, only as an expression. Use Date as your dimension and above(Date)-Date as expression. If it's a date type in SQL then you shouldn't need the date# function.

Re: Days difference between column of dates

Anonymous — Mon, 25 Mar 2013 13:25:13 GMT

Thanks again. I've added it as an expression but just see hyphens in the expression output. I'll try using the date# function to see if it makes a difference.

Re: Days difference between column of dates

Anonymous — Mon, 25 Mar 2013 14:08:28 GMT

In my Script I have: SET DateFormat='YYYY-MM-DD';

and in my table the date is right aligned so it appears Qlikview is recognising it as a date. As a test I added an expression =Date and then converted it to a number format and this looks correct also.

So, I'm confident the dates are recognised by Qlikview and I do not need to use the date# function but the expression results are still showing as null values.

Any other suggestions?

Thanks

Re: Days difference between column of dates

swuehl — Mon, 25 Mar 2013 14:13:00 GMT

Are you using more than one dimension? Take care of the so called column segments. You may need to use the TOTAL qualifier with the above() function.

Re: Days difference between column of dates

Anonymous — Mon, 25 Mar 2013 14:35:39 GMT

Thanks for the suggestion. Above (Total Date) - Date seems to have done the trick.