Attempting to use the no. of days between two dates as a calculated dimension?

[#1]

Hi please advise...

Am currently using:

=Interval(date(Tran_date,'DD-MM-YY')-date(Film_OpeningDate,'DD-MM-YY'),'d')

as the calculated dimension in a bar chart, but this returns a single bar, rather than individual bars for separate numbers of days?

Many thanks,

[swuehl]

I think this should / could work.

You don't need to format your dates using date() functions inside the interval() function, but you need to take care that both dates have a numeric representation (i.e. are QV dates). If you need to interpret strings as dates, use the date#() function instead.

Your single bar, what is its axis label? And what is the value range of your Tran_date and Film_OpeningDate fields?

(I assume both fields are located in one table, right?)

[#3]

Hi Swuehl,

The axis label is currently showing a blank (or '-') - it needs to display the number of days bewtwen the Tran_date and the Film_OpeningDate.

The value range should be something between 0 and -14.

Both fields are in separate tables?

Thanks

[swuehl]

Both fields are in separate tables?

This makes it a little more complicated.

How are your two tables linked and are your date fields linked with a 1:1 relation?

I think it would make it most easy if you can upload a small sample application here to the forum.

Regards,

Stefan

[#5]

Thanks for your help, the 'make date' function was used in the expression instead, it now works.