GetPivot Sum Multiple Column

[nicci113]

Hi there,

Guys, I have this work assisgnment where I have to extract data from pivot and display in combined fashion (below).
I can extract data and display using getpivot function but what if I want to Join A6 together and then display the result?
I also tried circular formulla but it doesn't seems to accept the value. Any help is appreciated.


[TABLE="width: 1006"]
<colgroup><col><col><col span="13"></colgroup><tbody>[TR]
[TD]Sum of Pax[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD] January
[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD] October
[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD="colspan: 2"]Grand Total[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD="align: right"]31542[/TD]
[TD="align: right"]10269[/TD]
[TD="align: right"]9518[/TD]
[TD="align: right"]9727[/TD]
[TD="align: right"]8159[/TD]
[TD="align: right"]8355[/TD]
[TD="align: right"]10087[/TD]
[TD="align: right"]9501[/TD]
[TD="align: right"]9766[/TD]
[TD="align: right"]10596[/TD]
[TD="align: right"]11577[/TD]
[TD="align: right"]11425[/TD]
[TD="align: right"]140522[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A6 [/TD]
[TD="align: right"]65388[/TD]
[TD="align: right"]60846[/TD]
[TD="align: right"]73843[/TD]
[TD="align: right"]42694[/TD]
[TD="align: right"]39413[/TD]
[TD="align: right"]40422[/TD]
[TD="align: right"]43421[/TD]
[TD="align: right"]44394[/TD]
[TD="align: right"]44395[/TD]
[TD="align: right"]45043[/TD]
[TD="align: right"]34485[/TD]
[TD="align: right"]35061[/TD]
[TD="align: right"]569405[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1006"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

How did you manage to get two identical row labels?

 

[nicci113]

How did you manage to get two identical row labels?

This is the airport data, A6 criteria has two different names within DB which result in two different record.

 

[nicci113]

This is the airport data, A6 criteria has two different names within DB which result in two different record.

One A6 has extra space and another don't which brings two records

 

[Andrew Poulsom]

Can't you remove the extra spaces from your source data? If not you will need to sum the results of two calls to GETPIVOTDATA.

 

[nicci113]

Can't you remove the extra spaces from your source data? If not you will need to sum the results of two calls to GETPIVOTDATA.

Not really I did tried that as well. I tried to sum of getpivot data and it seems to be working. It was more to do with when i was moving formula over it wasn't calculating the other fields but checked other forums and found the solution, thanks for headsup

 

[nicci113]

how to close the thread any idea, can't fiind the option