More specifically,
"=IF(OLD VALUE=0,1,IF(MIN(OLD VALUE,NEW VALUE)<=0,(NEW VALUE-OLD VALUE)/ABS(OLD VALUE),(NEW VALUE/OLD VALUE)-1))".
This formula is working great in my excel dummy tests (using RAND()*100 to generate dummy currency values). The 'C' column would be where the Percentage Change value lives. But I'm curious on how it would translate over in Tableau?
3 risposte
@Martin Valle
Two suggestions on sample data
- Make the sample tests all your conditions. You have a clause that checks for both values being less than 0, but did not have any cases where a number was less than 0. If you are not testing it, you can't be sure it works. (I added an extra row with a negative number)
- When providing sample data in a spreadsheet, it should not include extraneous text that can cause problems with the data loading
SUM(IF [Yr 2018]=0 AND [Yr 2019]>0
THEN 1
ELSEIF MIN([Yr 2018],[Yr 2019])<=0
THEN ([Yr 2019]-[Yr 2018])/ABS([Yr 2018])
ELSE ([Yr 2019]/[Yr 2018])-1
END)
