topic Re: Calculated field in pivot table in App Development

Calculated field in pivot table

Anonymous — Sun, 28 Aug 2016 19:21:59 GMT

Hi,

I have a pivot table where the invoice values are shown by month and year. I managed to write an expression to calculate the delta percentage: ([Current Year]-[Previous Year])/[Previous Year]. This is being displayed in a separate pivot table.

Now to save some space on the sheet and to make it visually more appealing I would like to incorporate both the invoice values and the delta percentages in one pivot table. As you can see in attached print screen simply pasting the delta percentage expression did not do the trick.

Does someone have a solution for this?

P.S. I already found some information in another thread (Add a calculated column to pivot table with set analysis) where the solution of a dummy dimension is mentioned. Unfortunately I do not understand how this works.

Re: Calculated field in pivot table

sunny_talwar — Sun, 28 Aug 2016 19:48:13 GMT

Is this what you want:

(Sum([Invoice Value]) - Before(Sum([Invoice Value])))/Before(Sum([Invoice Value]))

Re: Calculated field in pivot table

Anonymous — Mon, 29 Aug 2016 06:35:36 GMT

Hi Sunny,

That's 99% what I was looking for. Thx.

About the 1%. Is there a possibility to hide the below column as this is not relevant or is this inherent to a pivot table?

Re: Calculated field in pivot table

sunny_talwar — Mon, 29 Aug 2016 09:48:18 GMT

Is this always going to be a comparison between two years or could there be more than 2 years in this chart?

Re: Calculated field in pivot table

Anonymous — Mon, 29 Aug 2016 09:54:29 GMT

It's always going to be a comparison between two years.

Re: Calculated field in pivot table

sunny_talwar — Mon, 29 Aug 2016 12:22:08 GMT

Would you be able to re-run the application for me with a new table in it?

Dim:

LOAD * INLINE [

DIM

];

Re: Calculated field in pivot table

Anonymous — Mon, 29 Aug 2016 13:05:27 GMT

Sure. See attachment.

Re: Calculated field in pivot table

sunny_talwar — Tue, 30 Aug 2016 12:36:30 GMT

My bad Tom, would you be able to run it with this:

Dim:

LOAD * INLINE [

DIM

];

I underestimated the need for the 3 in the DIM field. I apologize for asking over and over again to do this

Re: Calculated field in pivot table

Anonymous — Tue, 30 Aug 2016 12:57:14 GMT

No problem Sunny. It's you that is helping me so I don't mind.

Re: Calculated field in pivot table

sunny_talwar — Tue, 30 Aug 2016 17:00:14 GMT

Try the attached

Re: Calculated field in pivot table

Anonymous — Wed, 31 Aug 2016 07:44:49 GMT

That's 100% what I was looking for. Thx.

I consider the expression you wrote rocket science

Re: Calculated field in pivot table

sunny_talwar — Wed, 31 Aug 2016 08:50:45 GMT

Hahahaha, the good thing is that I was able to help you out at last. and I ended up needing only DIM 1 and 2. Sorry to make you rerun the app with 1,2, & 3