topic Re: Vertical calculation on a horizontal dimension in pivot table in QlikView

Vertical calculation on a horizontal dimension in pivot table

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am aggregating sales value on 3 dimensions in a pivot table(Zone,city,month(horizontal)).How do I calculate vertically on a horizontal dimension. For example, I want to calculate AvgQ1 as avg(Apr-15,May-15,Jun-15) as shown below.

Could you please help me on this? Thanks in advance.

		Apr-15	May-15	Jun-15	Avg Q1	Jul-15	Aug-15	Sep-15	Avg Q2
North	Delhi	421	410	401	411	432	451	319	401
	Agra	1577	1523	1644	1581	1886	1929	1555	1790
	Jaipur	180	172	180	177	194	206	209	203
	Lucknow	107	127	149	128	126	137	154	139
	Chandigarh	272	279	250	267	295	294	282	290
	Varanasi	235	217	237	230	254	278	283	272
	Gurgaon	187	204	212	201	224	217	244	228
North Total		2980	2932	3073	2995	3412	3512	3046	3323

Re: Vertical calculation on a horizontal dimension in pivot table

swuehl — Sun, 14 Aug 2016 14:19:59 GMT

You can create a field that groups your year month values into quarters in the script (e.g. using a master calendar), then use the Quarter field as additional horizontal dimension on top of the year month field.

Use a partial sum on presentation tab on the quarter field and an expression like

=If(SecondaryDimensionality() = 1,

Sum(Sales) / Count(DISTINCT Month),

Sum(Sales)

)

Re: Vertical calculation on a horizontal dimension in pivot table

Mon, 15 Aug 2016 08:07:34 GMT

Thank you Stefan for this idea.

After applying this logic , I am getting in the below format. But I wanted to display quarterly average next to month values as shown earlier.

Re: Vertical calculation on a horizontal dimension in pivot table

swuehl — Mon, 15 Aug 2016 08:19:36 GMT

I suggested to use only one expression, not creating a second one for the average.

The expression should branch into two evaluations for your details and the partial sum average based on Dimensionality() / SecondaryDimensionality() function.

You just need to enable partial sums for the correct dimensions.

Re: Vertical calculation on a horizontal dimension in pivot table

Mon, 15 Aug 2016 09:33:49 GMT

Thank you Stefan for correcting my mistake. Now its working and I have got as below. Is it possible to not show quarterly dimension row and show partial sum label as 'Avg Q1'.