COGNOiSe.com - The IBM Cognos Community

HI folks,
I have the following historical data for the same case shown below,
Car ID            Arrive Date   Repair Date   Sold Date     Days in the Dealership
1111              02/01/2017  03/01/2017   04/15/2017   Sold Date-Arrive Date
1212              03/01/2017  04/01/2017   05/17/2017   Sold Date-Arrive Date

So, the problem is the rows is as following in the database
Car ID            Arrive Date   Status
1111              02/01/2017  Arrive
1111              03/01/2017  Repair
1111              04/15/2017  Sold

So, I don't know how to establish a calculation for Sold Date-Arrive Date since it is
not in column format like the first sample.

Any help is very much appreciated.

Thanks :) :)
                     

Quote from: gosoccer on 31 Aug 2017 08:35:48 AM
HI folks,
I have the following historical data for the same case shown below,
Car ID            Arrive Date   Repair Date   Sold Date     Days in the Dealership
1111              02/01/2017  03/01/2017   04/15/2017   Sold Date-Arrive Date
1212              03/01/2017  04/01/2017   05/17/2017   Sold Date-Arrive Date

So, the problem is the rows is as following in the database
Car ID            Arrive Date   Status
1111              02/01/2017  Arrive
1111              03/01/2017  Repair
1111              04/15/2017  Sold

So, I don't know how to establish a calculation for Sold Date-Arrive Date since it is
not in column format like the first sample.

Any help is very much appreciated.

Thanks :) :)
                     

Hi,

I'm sure we've been through similar examples before? The approach is to "bucket" the dates based on status, and then take the maximum of each date, eg

maximum(if ([Status] = 'Arrive') then ([Arrive Date]) else (null) for [Car ID ]) - this would be your Arrival Date
maximum(if ([Status] = 'Repair') then ([Arrive Date]) else (null) for [Car ID ]) - this would be your Repair Date
maximum(if ([Status] = 'Sold') then ([Arrive Date]) else (null) for [Car ID ]) - this would be your Sold Date

You can then use _days_between() to calculate the days in dealership:

_days_between([Sold Date], [Arrival Date])

Cheers!

MF.

Sorry about that if we have discussed it in the past. :) :)

So, basically, create three calculations within FM Model under the Subject Query so
they'll be available as a part of the package in Workspace Advanced, Right?

So, they just drag these different basically measures to get their information.

Or

They might be able to do it directly in Workspace Adv.

Thanks for your time as always!!

Thank you! Thank you! Thank you!
The recommended approach works like a charm!

I was able to enjoy the Labor's Day!

:) :)