Hello All,
I am trying to incorporate a YOY Profit Variance formula in cross table using the following data:
Age Group: 18-25, 26-35, 36-55, 55+
Profit: $DOLLAR
Activity Date
Current/Previous Year Flag: CY, PY
My ideal output would be:
CY - Current Yr PY - Current Yr Var.
Age 18-25 $ 146,888 $ 104,138 41%
Age 26-35 $ 91,169 $ 111,672 -18%
Age 36-55 $ 120,281 $ 86,284 39%
Age 55+ $ 144,021 $ 92,877 55%
Total $ 502,359 $ 394,971 27%
I tried the following formula however; it did not work
[(IF Current/Previous Year Flag = CY
RETURN PROFIT) /
(IF Current/Previous Year Flag = PY
RETURN PROFIT)] -1
format: PERCENT
Can someone help please?
Thank you,
CJ
CJ, Did you try to nest the formula? My example is: ( 'Sales Rep Actual'n - 'Sales Rep Target'n ) / 'Sales Rep Target'n and set the format to Percent. I have included a couple of screenshots. Shannon PS You MAY need to check the aggregation.
To complement what @ShannonMoore said, you would need to derive the equivalent of Shannon's Sales Rep Actual and Sales Rep Target. In your case, those would be Profit CY and Profit PY, defined respectively as:
IF Current/Previous Year Flag = CY
RETURN Profit
ELSE missing
IF Current/Previous Year Flag = PY
RETURN Profit
ELSE missing
Best,
Renato
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.