Sum of rows in pivot table

[Anonymous]

I've searched in this forum and haven't found my exact issue raised, wondering if this is possible: I have a pivot table which can be expanded at various nodes by the user and a Target column which is calculated based on existing data (does some rounding). They decide what level each Target needs to be calculated at, by expanding or collapsing dimensions. But I need the total rows to show the total of the lower level rows, not recalculate with rounding, as currently totals do not add up.

In the chart below, the total for Company 1 (and Division 1) should be 151, not 155. I played around with dimensionality() and aggr() but the formula will be different depending on what nodes have been expanded. Anyone know how I can do this?

[vishsaggi]

Yes.

[vishsaggi]

May be the aggr field list for Dimensionality () 2 and 3 should have all three Dimensions. I might be wrong.

=If(Dimensionality() = 5,
Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')), $(=GetObjectField(3, 'CH01')), $(=GetObjectField(4, 'CH01')), $(=GetObjectField(5, 'CH01')))),
If(Dimensionality() = 4,
Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')), $(=GetObjectField(3, 'CH01')), $(=GetObjectField(4, 'CH01')))),
If(Dimensionality() = 3,
Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')), $(=GetObjectField(3, 'CH01')))),
If(Dimensionality() = 2,
Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')), $(=GetObjectField(3, 'CH01')))),
If(Dimensionality() = 1,
Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')), $(=GetObjectField(3, 'CH01')))))))))

[sunny_talwar]

I think that will work for the current expression... but based on different collapse and expands, it may not work...

[sunny_talwar]

This will work but with it's own headaches

=If(Max(Dimensionality())-1 = 5,

Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')), $(=GetObjectField(3, 'CH01')), $(=GetObjectField(4, 'CH01')), $(=GetObjectField(5, 'CH01')))),

If(Max(Dimensionality())-1 = 4,

Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')), $(=GetObjectField(3, 'CH01')), $(=GetObjectField(4, 'CH01')))),

If(Max(Dimensionality())-1 = 3,

Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')), $(=GetObjectField(3, 'CH01')))),

If(Max(Dimensionality())-1 = 2,

Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')), $(=GetObjectField(2, 'CH01')))),

If(Max(Dimensionality())-1 = 1,

Sum(Aggr(Round(Sum(Count) *.9, 1), $(=GetObjectField(1, 'CH01')))))))))

[sunny_talwar]

Tested with some more data... it seems to be working.... but I still feel that there might be a case where this might just fail...

[bc-thebruuu]

hmm ...

in order to check I just added a column

$(=GetObjectField(4, 'CH01'))

it turns out that this return the value of the column and not its name...

So why does it work?

[sunny_talwar]

Don't use the dollar sign expansion when you are trying to see what it gives... dollar sign expansion is used to just tell the output that hey output you are field name....

Try this

=GetObjectField(4, 'CH01')

and then dollar sign expansion comes in and do it's trick to convert word Zone to a field Zone

Does that make sense?

[Anonymous]

Thank you so much for all this work Sunny!

I just reloaded with some additional data though, and found an issue with the totals for Division 2 below. It really seems like a complicated way to do something supposedly simple?

[sunny_talwar]

It really seems like a complicated way to do something supposedly simple?

Can't agree with your more

I just reloaded with some additional data though, and found an issue with the totals for Division 2 below.

Like I mentioned here

One person I can think of who might be able to find a solution: swuehl‌

[sunny_talwar]

Actually, I was trying to see what the problem is... but couldn't find it... which total is wrong?

Looks good to me... no?

80+75+50 = 205

and 205+156 = 361

What is not right?