topic Dimensions level difference in pivot table. in QlikView

Dimensions level difference in pivot table.

Tue, 10 Jan 2012 06:27:08 GMT

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

Dimensions level difference in pivot table.

vijay_iitkgp — Tue, 10 Jan 2012 07:18:18 GMT

Hi,

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

Dimensions level difference in pivot table.

vijay_iitkgp — Tue, 10 Jan 2012 07:19:43 GMT

Srry..

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

Dimensions level difference in pivot table.

vijay_iitkgp — Tue, 10 Jan 2012 07:23:48 GMT

Or if want to use set analysis then:

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

Dimensions level difference in pivot table.

Tue, 10 Jan 2012 07:54:13 GMT

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

Dimensions level difference in pivot table.

vijay_iitkgp — Tue, 10 Jan 2012 07:57:59 GMT

Then Split the expression...

Use 1st part for Proposed

2nd Part for Actual

and complete for difference

Dimensions level difference in pivot table.

Tue, 10 Jan 2012 09:18:59 GMT

you can apply expression in pivot table as:

sum(proposed) - sum(actual).

Dimensions level difference in pivot table.

vijay_iitkgp — Tue, 10 Jan 2012 09:21:59 GMT

Vijit,

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

Dimensions level difference in pivot table.

Tue, 10 Jan 2012 09:42:48 GMT

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.

Re: Dimensions level difference in pivot table.

Wed, 13 Jun 2012 22:35:27 GMT

Hi Umesh,

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

Thanks,

Grif