topic Pivot table over columns formula in Dashboards & Analytics Discussions

Pivot table over columns formula

tomas_je — Tue, 27 Sep 2022 11:21:41 GMT

I have pivot table with years in columns, how can I calculate Y/Y diff inside the table?

Re: Pivot table over columns formula

JoeM — Tue, 27 Sep 2022 14:28:54 GMT

@tomas_je - you may find the 'result set' capability to be what you need to add an additional transformation. There is a year-over-year analysis example here.

Re: Pivot table over columns formula

RADSr — Tue, 27 Sep 2022 14:38:15 GMT

When I've done this I've had to go to formula columns ( e.g. CASE(year=currentyear, amt, 0 ) - CASE( year=lastyear, amt, 0 ) ) kind of thing.

Re: Pivot table over columns formula

tomas_je — Tue, 27 Sep 2022 15:19:22 GMT

I have found that capability in docs, but seems it is available only in cloud version - am I right? Because we use on-premise 5.1 now.

Re: Pivot table over columns formula

tomas_je — Tue, 27 Sep 2022 15:21:28 GMT

I tried similarly but this is not working, because data in Pivot table are filtered already only for the year in the column, so second part of the formula returns 0.

Re: Pivot table over columns formula

RADSr — Tue, 27 Sep 2022 15:50:50 GMT

Yes. I removed the year filter. I also added a day-number-of-year ( on 9/27 the value is 270 ) filter so that e.g. 2022 would include up to today and so would 2021.

Another approach is to build an MV with current year, past year, YoY difference as separate columns.

Re: Pivot table over columns formula

JoeM — Tue, 04 Oct 2022 21:26:53 GMT

@tomas_je - it's available on-prem starting on 5.2!