topic Re: Custom total calculation in Pivot table in QlikView

Custom total calculation in Pivot table

Anonymous — Wed, 19 Feb 2014 09:25:26 GMT

Hi all,

I have a requirement to show the total in a format.

My chart will look int the above format. In the place of Total row I need the calculation as I mentioned in the above pic. Need to add the first three rows value and subtract next three rows value.

Can anyone help me in this.?

Thanks,

Leni Balakrishnan

Re: Custom total calculation in Pivot table

Sokkorn — Wed, 19 Feb 2014 09:41:35 GMT

Hi Leni,

1. First load this sample script

Data:

LOAD * Inline [

Type, Country1, Country2,

T01, 423, 432

T02, 456, 678

T03, 123, 321

T04, 789, 123

T05, 456, 789

T06, 121, 444];

2. Create Straight table

a. Dimension: Type

b. Expression:

Country1 = If(Dimensionality()=0,Sum({$<Type = {'T01','T02','T03'}>} Country1) - Sum({$<Type = {'T04','T05','T06'}>} Country1),Sum(Country1))

Country2 = If(Dimensionality()=0,Sum({$<Type = {'T01','T02','T03'}>} Country2) - Sum({$<Type = {'T04','T05','T06'}>} Country2),Sum(Country2))

See attached file.

Regards,

Sokkorn

Re: Custom total calculation in Pivot table

Siva_Sankar — Wed, 19 Feb 2014 09:52:36 GMT

Right click on the object-- > presentation---> select dimension ---> check on show partial sum

add your expression like ( Column(1) + Column(2) + Column(3) )

Regards.

Siva

Re: Custom total calculation in Pivot table

Wed, 19 Feb 2014 09:57:32 GMT

Write this expression in Expression tab,

Change the country name for every Expression.

Sum({$<Type = {'Type1','Type2','Type3'}>}CountryName)- Sum({$<Type = {'Type4','Type5','Type6'}>} CountryName)

May it works Try once let me know

Regard

Venu