Correct aggregation of two dates in fact

[#1]

Hi

I've put together a generic sample of a problem I am trying to solve in the attached qvw. The example is of a rental store that rents out dvd's and cd's. The fact table contains a rental date and a return date for each rental transaction. I have created a calender for rental date and return date.

The sample contains two straight table charts.

Rentals and Returns : shows the amount of 'rentals out' by rental date (count of the rentals per rental day) and 'rentals in' column (is a sum of rentals returned by rental date).

Returns: list a sum of rentals returned by return date

Problem: I would like the 'rentals in' column in the Rentals and Returns Straight Table Chart to be the value by return date rather than rental date, in other words it must be the same as the Returns Straight Table Chart

The bar graph is reflective of the same problem, the rentals for Jan, Feb and Mar are correct, but I woudl like to see how many returns I had in Jan, Feb and Mar, irrespective of when it was rented out.

I would appreciate in assistance.

Kind Regards

[#2 · Accepted solution]

Hi,

You need to store the data in a different manner in order to solve this problem. In the attached qvw file, I have created a new table and a new chart which shows correct data. Hope this helps..

Thanks

Amit

[stephencredmond]

Hi,

I think that the correct way to solve the particular problem is to change the way that rentals are associated with dates.

My solution uses only one date table and then relates each rental to the date with a status - either Rental or Return - which can be counted.

It appears to be correct because my chart shows returns in April when there were no rentals.

Regards,

Stephen

[#4]

Thanks very much chaps, this has worked a charm. I am trying to do some calculations with the definition but am running into trouble. How would I state the definition if I wanted to count the number of returns that occured on the same day as being rented, and then similarly the count of returns where the return date is greater than the rental date (cases where the item was returned any day after initally being rented).

Kind Regards