topic Re: How to make operation between column in a Pivot Table in App Development

How to make operation between column in a Pivot Table

Cap — Thu, 14 Jul 2022 21:33:48 GMT

Hello,

here is a screen of the data:

I would like to be able to make a Current Year / Last Year Ratio (for example 114.24€/38.15€) but i can't find the correct expression for it.

I tried:

sum({<Year={"$(=Year)"}>}Sales)/sum({<Year={"$(=Year-1)"}>}Sales)

It is returning me a null value unfortunately 😞

What am i missing please ?

Re: How to make operation between column in a Pivot Table

sidhiq91 — Fri, 15 Jul 2022 01:26:26 GMT

@Cap I see the approach is right for calculating the ratio. Would you make the changes as shown below and see if that is working for you.

(Sum({<Year={"$(=Max(Year))"}>}Sales)/Sum({<Year={"$(=Max(Year)-1)"}>}Sales))

If it did, please like and accept it as a solution.

Re: How to make operation between column in a Pivot Table

Cap — Fri, 15 Jul 2022 19:37:25 GMT

Hello Sidhiq91,

Unfortunately it doesn't and send 0 for each value 😞

If i set the expression to :

Sum({<Year={"$(=Max(Year))"}>}Sales)

Every value is equal to zero EXCEPT for the colum equivalent to max year (in this case 2020), same process if i put max(year)-1 -> 2019, etc.

The expression doesn't seem to work if the <Year> is not correspondant to the collumn selection, witch i don't understand at all

Re: How to make operation between column in a Pivot Table

sidhiq91 — Sat, 16 Jul 2022 02:29:10 GMT

@Cap Could you please provide me some sample data? Let me try to work it out for you.