How to add a variance into a pivot table

Adam Wickes

Hi all,

I've seen the "variance in a pivot" question asked around here a fair bit and and happy that this is fairly easy using a calculated item.
My issue seems a little more complex though.

Basically, i'm trying to create the following in a pivot. 

		Debit/Credit	Debit	Debit	Debit	Credit	Credit	Credit	Variance
		Product	P1	P2	P3	P1	P2	P3	Variance
Branch	Region	Name	Amount	Amount	Amount	Amount	Amount	Amount	Amount
1111	1	TestBranch	30	5	5	50	3	20	33
2222	2	TestBranch2	10	20	30	1	5	10	-34

I can't seem to work out how to get the variance column to be the total debits subtract the total credits but only have the single column at the end.
I've tried adding a union with a "variance" value in the debit/credit and product columns but it seems to act strangely.
I was hoping there would be an easier way that I wasn't aware of.

Thanks in advance,
Adam

asim cholas

You can create a new column in criteria like below.

SUM(case when ColumnDebit/Credit ='Debit' then Amount) - SUM(case when ColumnDebit/Credit ='Credit' then amount)

and place it in the Rows Area of Pivot. the only thing is variance will come first.

Robert Angel

Whenever there is a combination of case and summation logic my best recommendation is to put it in a physical calculation in the subject area in the rpd.

If this is not an option then sum(case... etc as recommended by @Asim should work, but you may also need to set the properties on that column to server complex aggregate or you may find strange results as you have been seeing, the strange results are because OBIEE to optimise speed as a bias towards logical calculations, and in the case (no pun intended) of your sum (case logic...etc logic it cannot aggregate first and get the correct result.

asim cholas

When it is placed in Rows area how aggregation matters ?

Adam Wickes

I have already tried your suggestion above but for some reason instead of subtracting one from the other, it adds them together.
I have also tried multiplying one by -1 so that when it adds together, it effectively does the subtraction but it still adds the figures together but results in a negative of the value instead of a positive.
I will try and server complex aggregate suggestion first and if that doesn't work, i'll the value to the physical database.

Thanks for your suggestions guys. WIll update when I know.

PS I should have mentioned that the order of the columns is very important as the data is being consumed by a downstream process that cannot be changed easily.

Adam Wickes

Ok so it seems that i'm still missing something.

I've simplified it down to one branch to see if I can see what is happening.

I start off with this (Note: I have multiplied the Debits by -1 so that they're shown as negatives instead of positives):

Branch	Product	Debit/Credit	Amount
1	P1	Debit	-13,142
1	P1	Credit	49,331
1	P2	Debit	-164
1	P2	Credit	292
1	P3	Debit	-5,615
1	P3	Credit	34,450

Now, the next step is to change the product field to the word 'Variance', which effectively changes the group by.

Branch	Product	Debit/Credit	Amount
1	Variance	Debit	-18,921
1	Variance	Credit	84,073

So far, so good.... Now we want to change the debit/credit to the word 'Variance' as well so we are left with only one row.
My expectation is that it will add the two numbers above together and we will be left with 65,152.

The result is:

Branch	Product	Debit/Credit	Amount
1	Variance	Variance	102,993

I have tried using server complex aggregate, server determined and sum as the default aggregation on the field.
All give the same result.

Any ideas? It seems to work as expected until I remove the debit/credit field.

Thanks,
Adam

Robert Angel

cf my previous comment that you would be better doing this in the rpd.

But, if it must be answers, have you tried using the filter functionality in function editor to create the figures, you can nest filters to achieve your ends.

Or, what happens if you keep the DR / CR field but use display:none to suppress it, then pivot?

asim cholas

I dont think it will add to the results .. I will show you an example.

Here i kept debit - credit

In OBIEE Pivot as far as i know we cannot add a column after the pivoted columns.

Adam Wickes

Hi Asim,

This is fine but as mentioned, I need the Variance field to remain the last column in the pivot.

Robert,

Could you please advise how you would do this in the RPD?

Adam Wickes

Worked it out guys... For those with a similar issue, you can use the Filter function instead of the Case function and for some weird reason, it works as intended.
The formula I used for my amount measure is as follows:

FILTER("Transactions"."Tran Amount - Including Reversals" USING ("Transactions"."Debit Credit" = 'Debit')) - FILTER("Transactions"."Tran Amount - Including Reversals" USING ("Transactions"."Debit Credit" = 'Credit'))

I hope that helps somebody out in the future.

asim cholas

So How did you add it as the last column in pivot?

Also Filter function is better than wierd case, But unfortunately Oracle BI will convert the filter function to case statement when executing in DB as database doesnt know what is filter function