BookmarkSubscribeRSS Feed
chrisjab
Fluorite | Level 6

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

 

 

2 REPLIES 2
ShannonMoore
SAS Employee

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.


formula.PNGxtab1.PNG
Renato_sas
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 2 replies
  • 1057 views
  • 0 likes
  • 3 in conversation