difference in days in straight table

[#1]

Hi All,

I got a requirement as follows:

i have a data in straight table with 3 columns old brand,new brand and their brand dates.

Now i want one more column as difference in days when compared to below row date in a date column.

For example:

old	new	date	difference(Days)
prod a	ban a	24/06/2014	14
prod b	ban b	08/07/2014	0
prod c	ban c	08/07/2014	28
prod d	ban d	05/08/2014	22
prod e	bane	27/08/2014	9
prod f	ban f	05/09/2014	4
prod g	ban g	09/09/2014	-

In the above table, i want to show difference(Days) column in qlikview.

[PrashantSangle]

Hi,

Use Interval(DateField-below(DateField),'D')

Regards

[#3]

HI Max,

I tried with your solution but it is showing '-' in the table column.

[senpradip007]

PFA. Hope it will help.

[PrashantSangle]

Hi,

Try this

Interval(Date(DateField-Above(DateField)),'D')

Check DateField Format

Regards

[#6]

HI,

can anyone help me on this i am missing out something.

[JonnyPoole · Accepted solution]

The basic expression is as follow:

below( total Date,1,1) - Date

The 'TOTAL' is key so that it looks over all dimensions and not restricted to the current rows dimensional value

To ensure your values are read as a date you below( total Date,1,1) - Date can try the following as well based on the format you posted above:

below (  total  date(date#(Date, 'DD/MM/YYYY')) , 1, 1)  -  date(date#(Date, 'DD/MM/YYYY'))