Days between function using dates from two different datacubes 1

[DrSmyth]

Hi, I'm, tearing my hair out with this one...

I've got a report with linked data providers, basically the combined report has a two dates in it based on two queries that are from the same database, it looks something like this

Name Date1 Date2
a 01/01/2003 05/01/2003
b 02/04/2003 05/08/2003
c 07/07/2002 08/08/2002

I've had to unlink the dates in the report data manager as they are in fact the same field (with a different event). I want to calculate the number of days between date1 and date2. I've tried using the DaysBetween function but get a "Variables are not compatible DMB0008" error...

Does anybody have any ideas, or am i gonna have to stick it in Excel????

 

[blom0344]

One idea is to fetch the julian_date for both date fields. As this is an integer, it allows easy calculation of the difference between two dates by bringing it in as a measure.

In more generic terms, the use of calendertables for calculations and conversions is highly recommended......

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

Cheers T.Blom, do you know where I can find out about Calaender Tables?

 

[blom0344]

I use several calendertables in my datamarts. Some have been created just by creating them in Excel and then uploading them.
Others come from an ORACLE procedure that creates a special file, or from the datefile from our AS400 system.

If you need an example, let me know.......

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

Could you post an example (or mail it to me on andy.p.smith@hsbc.com)

Cheers...

 

[DrSmyth]

Cheers for the example T.Blom, this may sound a bit stupid, but how do i incorporate this into my universe/reporting? Do you know of a good source?

 

[blom0344]

That is the tricky bit.

If your universe stores one just one date field as a candidate, you can add the calenderfile through a join on the date fields. However, make sure that the calenderfile stores a wide enough range for the date, otherwise you will lose data from the facttable (through the effect of the inner join) Alternative is to use an outer-join to the calendertable, in order to be sure you do not miss out on data.

If you have more candidates for date-conversion and you want to you several dates in one DP, I would suggest creating a batch of database-views on the calendertable in order to access the calenderdata multiple times.

Be aware that just using aliases in designer is not sufficient, in order to have proper SQL statements you will need seperate views (as they are really different object, whereas an alias refers to the same object in SQL)

Calender tables based on dates can be used on facttables that store data at date-level.
On aggregates (for instance on month-level) use an appropriate calenderfile ( also month-level) to work with.

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DoubleStew]

Another option may be to do it in the report.

Create a variable, which you define as a measure, which just has the dimension <date value> from the second data provider as the formula. Make sure that you still unlink the dates in the report data manager.

Create another variable to do the days between calculation using the dimension <date value> from query 1 and the measure local variable <date value> from query 2 that you created.

You will not get the variables incompatible message and your days between calculation will now work.

 

[DrSmyth]

Works like a dream Double Stew.... That's a big Gold Star for you