Assigning a value to a year

[Skykit]

Hi all,
I have a slightly annoying warehouse I am working from and have hit another snag.

Example:
a car was bought for 1360.32 as a daily charge on 24th June 2015 and sold 24th June 2016
The daily charge for the car from the 1360.32 is 1360.32 / 365 giving a daily charge of 3.72 (don't worry about rounding/exact)

So how do I know how much that car cost in 2015 and how much in 2016?  easy, _days_between the start date and the end of year date for 2015 * daily charge
Same for 2016 but this time the start date is 1st of Jan 2016 to the date of the car being sold * daily charge

In a list format this works great BUT what the user wants is to almost pivot it but remain list format so rather than
Col A: Car registration, Col B: 2015 value, Col C: 2016 value
The requirement:
Col A: Car registration, Col B is a SPLIT of the two years, Col C is the amount corresponding to the car.  This method obviously would give that specific car TWO twos rather than one in the first working example above.

Given the amount is not assigned to the individual car for the year/s in question, how do I split that 1360 into two years based on the start/end date so that in a list format, it shows two lines rather than two columns with the value.

I hope this makes sense!

AJ

[bdbits]

Add year as a data item, and do the calculation with an if statement.
if ([year]=2015) then ( your 2015 calc ) else ( your 2016 calc)

[hespora]

I understood the requirement a bit differently (but maybe, if the op could maybe create a mockup in a spreadsheet, that might help).

The way I understood it: In your column B, insert another list object. select master-detail on that, and tie it back to the outer list by car registration.

Now, outer list: Col A = your registration, Col B = the inner list, Col C = your measure.
Inner List: just one column, pretty much the calculation bdbits has posted; add [year] to the properties of the list, and sort by [year]

This will obviously fail once you have either more, or different years than 2015 and 2016 in your data, but this should get you on the right track to experiment.

[Skykit]

Hi both,

Please find a mockup in excel but in a jpeg snapshot -  hopefully this makes more sense.
In this, the example breakdown relates to the blue lines.  The rest are junk lines just to give a perception on how it should look/is looking.

As you can see, it's not always 2015 or 2016 values.  in some cases it could be that the sold and bought date fall within the year (ie: bought 01/01/2016 and sold 31/12/2016) in which case only one line shows for 2016

Equally when it does fall over two calendar years, it could be 2017 to 2018 or 2019 to 2020 etc.

Essentially, I'm trying to create a virtual year so that when car reg is dragged in it's one row but when Year is dragged in, two appear if the bought and sold cross over two calendar years/one row if only one year. And of course when the value is dragged in, it will give the calculations accordingly which do currently work as per the "current view"

[SpareTire]

I've done this before and I do it ugly--so it would be nice to see someone with a bit more logical thinking chime in.

In short, make a query for every year as per bdbits suggestion calculating only that years days and value. Then union all the queries together to get it in the raw pivot data format ("required view")- the union gives you a new line for every year. If you have access to the database, writing this in SQL would be "neater" then doing it in Cognos.

[hespora]

ok, now this is based on the assumption that your raw data only includes four fields:

- registration
- cost
- buy date
- sell date

First thing you're going to need to get from *somewhere* if you want to use it as a data item is the year, and that should have every year between (year of oldest buy) and (current year). If you do not have a time dimension in your data source to grab the year from, create it manually via an sql item.

next, crossjoin your raw data with the years so you end up with every combination of registration and year. The formula that will calculate the cost incurred per year is:

Code Select Expand


[cost] / _days_between ([selldt], [buydt]) *

(
(
if(_days_between (_make_timestamp ([year]+1,1,1),[buydt])<0) then (0) else (_days_between (_make_timestamp ([year]+1,1,1),[buydt]))
-
if(_days_between (_make_timestamp ([year],1,1),[buydt])>0) then (_days_between (_make_timestamp ([year],1,1),[buydt])) else (0)
)

-

(
if(_days_between (_make_timestamp ([year]+1,1,1),[selldt])<0) then (0) else (_days_between (_make_timestamp ([year]+1,1,1),[selldt]))
-
if(_days_between (_make_timestamp ([year],1,1),[selldt])>0) then (_days_between (_make_timestamp ([year],1,1),[selldt])) else (0)
)
)


Lastly, just filter on cost incurred > 0, and you're done. See attachment as well