Custom total calculation in Pivot table

[Anonymous]

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

[Sokkorn · Accepted solution]

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

[Siva_Sankar]

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

or

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

Regards.

Siva

[#4]

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