Hello,
First, sorry for my bad english.
I work en COGNOS report studio and on a relational database, not a cube.
I want to make a difference/variation on a crosstab.
I have :
YEAR | Service | quantity
------------------------------------------
2015 | Compta | 1 500 $
2015 | STAT | 2 500 $
2016 | Compta | 500 $
2016 | STAT | 3 000 $
2016 | Human Ress | 4 000 $
Crosstab :
SUM(quantity) | 2015 | 2016
-----------------------------------------------
Compta | 1 500 $ | 500 $
STAT | 2 500 $ | 3 000 $
Human Ress | | 4 000 $
And I want :
SUM(quantity) | 2015 | 2016 | 2016 - 2015
-----------------------------------------------------------------
Compta | 1 500 $ | 500 $ | - 1 000 $
STAT | 2 500 $ | 3 000 $ | 500 $
Human Ress | | 4 000 $ | 4 000 $
How can I do that without manipulation on data (like create a value DIFFERENCE in YEAR)?
Thank
Quote from: Maxime Pavy on 14 Oct 2016 07:28:25 AM
Hello,
First, sorry for my bad english.
I work en COGNOS report studio and on a relational database, not a cube.
I want to make a difference/variation on a crosstab.
I have :
YEAR | Service | quantity
------------------------------------------
2015 | Compta | 1 500 $
2015 | STAT | 2 500 $
2016 | Compta | 500 $
2016 | STAT | 3 000 $
2016 | Human Ress | 4 000 $
Crosstab :
SUM(quantity) | 2015 | 2016
-----------------------------------------------
Compta | 1 500 $ | 500 $
STAT | 2 500 $ | 3 000 $
Human Ress | | 4 000 $
And I want :
SUM(quantity) | 2015 | 2016 | 2016 - 2015
-----------------------------------------------------------------
Compta | 1 500 $ | 500 $ | - 1 000 $
STAT | 2 500 $ | 3 000 $ | 500 $
Human Ress | | 4 000 $ | 4 000 $
How can I do that without manipulation on data (like create a value DIFFERENCE in YEAR)?
Thank
Hi,
With a relational model this is a little mode complex than with a dimensional model (cube). In your case, you're going to need some calculations:
2015 quantity: if ([YEAR] = 2015) then ([quantity]) else (0)
2016 quantity: if ([YEAR] = 2016) then ([quantity]) else (0)
You can then use these in the calculation you really want:
[2016 quantity] - [2015 quantity]
Cheers!
MF.
Hello,
First, thank for your answer.
I have read on function [ running-difference () ] (make difference on rows, not on columns)
but I never make it work like i want.
So it doesn't exist a solution for do what I want without manipulate data?
No need for me to search deeper?
Best regards