Days difference between column of dates

[Anonymous]

Hi

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

[Gysbert_Wassenaar]

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')

[Anonymous]

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".

[Gysbert_Wassenaar]

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.

[Anonymous]

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.

[Anonymous]

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

[swuehl · Accepted solution]

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.

[Anonymous]

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