Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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')
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".
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.
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.
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
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.
Thanks for the suggestion. Above (Total Date) - Date seems to have done the trick.