topic Re: Simple Table: Compare Years Sales - Totals column messing up percentage measure in App Development

Simple Table: Compare Years Sales - Totals column messing up percentage measure

SteveW — Sat, 16 Nov 2024 20:05:31 GMT

This is my first post and the first time I've tried to build a report, apologies if I'm asking basic questions!

I'm trying to build a basic table showing sales over the last 3 years and comparing this year with the previous year in the final column (2018 vs 2017) which is a percentage. I've used measures to achieve this.

The Total column is useful however it's coming in to play when calculating 2018 vs 2017 which is messing up my percentage. How do I exclude it from this calculation?

(I'm using test data so - it's old so I'm assuming the current year is 2018 for the purpose of building this report.)

Meausures:

mCurrentYear = sum(if(DTPR_Year =CurrentYear,AF,0))
mCurrentYearM1 = sum(if(DTPR_Year =CurrentYear-1,AF,0))
mCurrentYearM2 = sum(if(DTPR_Year =CurrentYear-2,AF,0))
mCYvsmCYM1 = (sum(if(DTPR_Year=CurrentYear,AF,0))-sum(if(DTPR_Year =CurrentYear-1,AF,0)))/sum(if(DTPR_Year =CurrentYear-1,AF,0))

mCYvsmCYM1 is a bit long winded but I also can't seem to access one measure from another so I had to recalculate those within it to arrive at the %age difference comparison.

Perhaps there's an altogether better way to achieve this than using measures in the first place?

Thanks

Steve

Re: Simple Table: Compare Years Sales - Totals column messing up percentage measure

sunny_talwar — Thu, 12 Sep 2019 10:44:38 GMT

"The Total column is useful however it's coming in to play when calculating 2018 vs 2017 which is messing up my percentage. How do I exclude it from this calculation?"

What exactly are you looking to exclude and from where? The Total for 2018 vs 2017 (21%)? The value should not be shown or are you expecting to see a different number?

Re: Simple Table: Compare Years Sales - Totals column messing up percentage measure

SteveW — Thu, 12 Sep 2019 10:51:41 GMT

Thanks for the reply.

You know I've just realised I'm being stupid.

This report is correct and my brain needs a reboot.

Sorry for wasting time.

Re: Simple Table: Compare Years Sales - Totals column messing up percentage measure

sunny_talwar — Thu, 12 Sep 2019 11:09:48 GMT

We all need reboots once in a while.. 🙂