Dimensions level difference in pivot table.

[#1]

Dear All,

I have dimension field as "Type" under this two categories Actual and Proposed. Now I want to get difference Proposed  v Actual  in pivot talbe.

I am unable to do it. can we do it ? if so please let me know.

sample  QVW file attached herewith for your reference.

Regrads

UG

[vijay_iitkgp]

Hi,

You can use Sum(If(Type='Proposed',Amount))-Sum(Type='Actual',Amount)

[vijay_iitkgp]

Srry..

Sum(If(Type='Proposed',Amount)) -Sum(If(Type=Actual,Amount))

[vijay_iitkgp]

Or if want to use set analysis then:

=Sum({$<Type={'Proposed'}>} Amount) - Sum({$<Type={'Actual'}>} Amount)

[#5]

Dear Mr. Vijay,

Thanks for prompt reply.But actually I want data in following format.

Type	Actual	Proposed	Difference
Vol	567.980	21.270	-546.710
InvPrice	4,909.20	4,400.00	-509.20
Excise	398.64	357.31	-41.33
VAT	640.33	573.95	-66.38
CST	0.00	0.00	0.00
Freight	846.30	846.30	0.00
Unloding	0.00	0.00	0.00
NCR	3,023.93	2,622.44	-401.50

Regares,

UMESH

[vijay_iitkgp]

Then Split the expression... 

Use 1st part for Proposed

2nd Part for Actual

and complete for difference

[#7]

you can apply expression in pivot table as:

sum(proposed) - sum(actual).

[vijay_iitkgp]

Vijit,

I think you overlooked the requirement.Proposed and Actuals are category not the amount. ...

[#9]

you can't apply this type of expression in pivot table as you have to apply expression differently for vol, invPrice etc.

so apply it in text object and place it in front of your pivot table:

expn.

= if(type = 'proposed', sum(vol)) - if(type = 'actual', sum(vol))

note: similarly for invPrice etc.

[#10]

Hi Umesh,

Did you find solution to your problem? I'm experiencing the same issue. If you have a solution please share.

Thanks,

Grif