topic Re: pivot table - getting expressions horizantal instead of vertical in QlikView

pivot table - getting expressions horizantal instead of vertical

infock12 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Hope you are all well. I am trying to do a pivot table. Dimensions are: Dept Code, Result, Target and Month. The expression is sum(OrderActual).

I can pivot things but the result I am trying to get is attached in the excel spreadsheet (End Result) and also pasted below.

I tried to add calculated dimension with the formula =sum({<Result={'AB'}>}OrderActual)/sum({<Result={'OPSRIC'}>}OrderActual), I get an error message.

As you can see below, I am trying to get AB, OPSPRIC, OPRate (which will be the result of the above expression), Target, and Variance TO Target (which would be 'Target-OPRate') - ALL HORIZANTALY just like below. In Background colour, I would like to say, IF(Target-OPRate)>0, Red, Green). Any help is appreciated.

Dept Code	Result	Month						At Month 6
Dept Code	Result	1	2	3	4	5	6	At Month 6
AC23Z	AB	2	0	1	4	0	3	10
	OPSRIC	597	641	730	677	677	695	4017
	OPRate	0.00	0.00	0.00	0.01	0.00	0.00	0.00
	Target	0.05	0.05	0.05	0.05	0.05	0.05	0.05
	variance to Target	-0.047	-0.050	-0.049	-0.044	-0.050	-0.046	-0.048
KC15S	AB	40	68	71	62	72	48	361
	OPSRIC	148	195	194	162	199	218	1116
	OPRate	0.27	0.35	0.37	0.38	0.36	0.22	0.32
	Target	0.05	0.05	0.05	0.05	0.05	0.05	0.05
	variance to Target	0.220	0.299	0.316	0.333	0.312	0.170	0.273

Thanks,

Karthik

Re: pivot table - getting expressions horizantal instead of vertical

sunny_talwar — Mon, 21 Nov 2016 17:37:20 GMT

Is this what you want?

Re: pivot table - getting expressions horizantal instead of vertical

infock12 — Mon, 21 Nov 2016 18:05:10 GMT

Hi Sunil,

exactly what what I wanted. I will look at it when I reach home. Thank you very much. I don't have Qlikview server access. I will give it a go and let you know.

Re: pivot table - getting expressions horizantal instead of vertical

sunny_talwar — Mon, 21 Nov 2016 18:53:20 GMT

Sounds like a plan. Btw my name is Sunny (not Sunil) my friend

Re: pivot table - getting expressions horizantal instead of vertical

infock12 — Mon, 21 Nov 2016 19:04:02 GMT

‌oops, sorry Sunny! I think it was the auto text!

Re: pivot table - getting expressions horizantal instead of vertical

sunny_talwar — Mon, 21 Nov 2016 19:05:04 GMT

no problem

Re: pivot table - getting expressions horizantal instead of vertical

infock12 — Tue, 22 Nov 2016 11:58:25 GMT

Hi Sunny,

Thanks again for the response and it is working great! However, I tried to colour code the 'Variance to Target' row. In principle, if Target > OPRare, it should be red, otherwise green. I tried various combinations that you have used under 'Expressions' but I am doing something wrong. The closest I could get to (still not correct) is with the below, but it highlights the OPRare and the Target field rather than the 'Variance to Target' field. Clearly, I am doing something wrong. Please could you point me in the right direction.

=PICK(Dim, Num(Sum({<POD={'DC'}>}ActivityActual)/sum({<POD={'OPPROC'}>}ActivityActual), '##.00')>Target, Green(), Red())

Thanks,

Karthik

Re: pivot table - getting expressions horizantal instead of vertical

sunny_talwar — Tue, 22 Nov 2016 12:04:37 GMT

This?

Background color expression

If(Dim = 4,

If(Sum({<Result={'AB'}>}OrderActual)/sum({<Result={'OPSRIC'}>}OrderActual) > Target, Green(), Red()))

Re: pivot table - getting expressions horizantal instead of vertical

infock12 — Tue, 22 Nov 2016 12:23:23 GMT

That is it!! Thank you very much Sunny, extremely helpful.