If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

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

Started by DistillaTruant, 27 Dec 2018 03:19:22 AM

Previous topic - Next topic

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

Quote from: adam_mc on 28 Dec 2018 08:27:04 AM
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.
Meep!

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.