Year on Year percentage difference - as a Query Calculation/column

[DistillaTruant]

Hi All,

I'm using Cognos 10.2.2 - in Report Studio.

Basically I'm looking for an expression to create a data item/column that will show the % difference between Current Year and Previous Year data.

CY and PY is defined in a query calculation by the following logic which uses dates added by a prompt/filter:

if ([Return Date] >= _add_years ( ?Drop Off Start?, - 1) and [Return Date] <= _add_years ( ?Drop Off End?, - 1))
then ('PY')
else ('CY')

So the simple logic in Excel (which I'm trying to replicate) would have just been something like:

('CY'/'PY')-1

I've trawled through so many forums and web pages but can't seem to find an answer - Preferably don't want to go down the route of create a CY query, a PY query and joining query.

Still quite new to Report Studio so appreciate any help!

Thanks.

[adam_mc]

Try this, especially if you wish to display in a list.

First create two calculations one for CY and another for LY - I'm, not sure of your syntax, so I'm making the calculations somewhat generic:

CY = If ([Return Date] between "CY Start" and "CY End" then [CY] else 0 end
LY =  If ([Return Date] between "LY Start" and "LY End" then [LY] else 0 end

Then, set the aggregate of these to be totals.
This will sum all your CY totals plus all Zeros from any LY totals into the CY calculation - Similarly for LY, but in reverse.

Then, create a third calculation to do your Diff%.
Set this aggregate to be a calculation.

Hope this helps.
Adam.
     

[DistillaTruant]

Hi Adam_mc

Thanks for your reply; it's throwing up errors when I try to validate the calculation - CY example below, am I missing something?

If ([Rentals].[Return Date Details].[Return Date] between ?Rental Drop Off Start? and ?Rental Drop Off End? then [CY] else 0 end

The start and end dates are set by a prompt.

Thanks

[adam_mc]

Sorry, the syntax is wrong:

The calculation for CY should be:

      If ([Rentals].[Return Date Details].[Return Date] between ?Rental Drop Off Start? and ?Rental Drop Off End?) then ([CY]) else (0) end.

Also, to be clear, [CY] needs to be a numeric value (not the value 'CY').
Repeat fot LY.

Thanks,
Adam.

[MFGF]

Sorry, the syntax is wrong:

The calculation for CY should be:

      If ([Rentals].[Return Date Details].[Return Date] between ?Rental Drop Off Start? and ?Rental Drop Off End?) then ([CY]) else (0) end.

Also, to be clear, [CY] needs to be a numeric value (not the value 'CY').
Repeat fot LY.

Thanks,
Adam.

Hi,

There's a spurious end to that expression - leading to a sticky end? :)

For an if-then-else it would be

if ([Rentals].[Return Date Details].[Return Date] between ?Rental Drop Off Start? and ?Rental Drop Off End?) then ([CY]) else (0)

Cheers!

MF.

[adam_mc]

Indeed you are correct again, Muppet!

Hopefully, the logic for solving the original problem is correct.

[DistillaTruant]

Thanks both  :)

 

In the examples given, what would [CY] reference to?

 

Just to make sure that I'm being clear about what I need; I currently have this calculation to calculate what would be CY and what would be PY/LY:

 

if ([Return Date] >= _add_years ( ?Drop Off Start?, - 1) and [Return Date] <= _add_years ( ?Drop Off End?, - 1))

then ('PY')

else ('CY')

 

I then (just recently) found and adapted this expression to get my year over year variance:

 

total(

  case

  when [CYPY] = 'CY'

  then total([Rentals])

  else null

  end)/

total(

  case

  when [CYPY] = 'PY'

  then total([Rentals])

  else null

  end)-1

 

It seems to work but the whole thing feels clunky and I want to be sure it's working as efficiently as possible! The report runs a little slow... we migrated from Business Objects to IBM Cognos and now have to create a few slightly more complicated reports for the end user but this part seems to be causing the most hassle! Now to figure out the yoy variance for the yield per day calculations... :(

 

Thanks again.

 

PS. I was unable to reply previously and getting a Tech Data error so it's only just let me!

[adam_mc]

Actually, in both calculations, [CY] and [LY] represent the same metric (Rentals I think in your case).

So, you are creating CY Rentals and a LY Rentals calculations by selecting and totaling values for the specific date range in question.
If it is not in the date range in question, it falls to the 'else' condition and adds zero to the total.