Showing an expression columns at the end of pivot table after Pivoting a dimension

[sekharQV]

Hi All,

I have a requirement to show an expression columns separately at the end of pivot table after pivoting a year dimension.

The attached image is having expected output of requirement with pivot table.

Here we need to pivot year dimension and gross amount is shown under years.

After that I need to show open amount and totals  as a column.

I have tried this using calculated dimensions and i am able to achieve this.(But columns cannot be moved to end).

But the problem is with performance.

So i am looking for some other good solution.

I have attached the sample data file and QVW file congaing my solution using calculated dimension.

Thanks in Advance,

Rajasekhar P.

[Anil_Babu_Samineni · Accepted solution]

Like this?

[its_anandrjs]

Check the attached

Add new Dimension Inline table

LOAD * Inline [

Dimension

1

2

3

];

[its_anandrjs]

In your pivot table add new field as dimension

How to use Pick() in the pivot table here

Dim:-

DivisionName

=Pick(Dimension, [Invoice Year], 'Open Amount','Total Activity')

Expression:-

Pick(Dimension,sum([Invoice Amount]),Sum([Open Amount]),Sum([Open Amount])+Sum([Invoice Amount]))

[sekharQV]

Thanks Anand for detailed explanation