Sum of multiple filtered values

ConstelliumShawn

Hi,

I am trying to create a % by taking a filtered value in Column A and dividing it by other, multiple filters in Column A. I am able to create the numerator, however, when creating the denominator, I can't get it to register more than one filter. I've tried 'AND' and 'OR' statements, but no avail. See below:

SUM(CASE

WHEN Material Description = 'SALT FLUX-57.5/37.5/5'THEN Quantities Reverse ELSE 0

END)

/

(SUM(CASE

WHEN Material Description = 'CLASS I 3004 BODY STOCK SCRAP' OR '3104/3204' THEN Quantities Reverse ELSE 0) +
(CASE WHEN Material Description = '3XXX RSI(DROSS)' THEN Quantities Reverse ELSE 0) +
(CASE WHEN Material Description = '3003 CLEAR/BARE' THEN Quantities Reverse ELSE 0))

END

As stated, the denominator only registers the first case ('CLASS I 3004 BODY STOCK SCRAP' OR '3104/3204' in this case). Any advice on how to group the denominator filters?

Also, I tried creating dynamic segments for each of these and did, but don't know if I can reference dynamic segments in formulas. I tried but got not result.

MichelleH

@ConstelliumShawn It looks like your first CASE statement was the only one grouped inside the SUM. Rather than using separate CASE statements for each value of Material Description, you can structure your denominator to use a single CASE statement with each condition separated by WHEN… THEN… like this:

SUM(CASE
  WHEN `Material Description` = 'CLASS I 3004 BODY STOCK SCRAP' THEN `Quantities Reverse`
  WHEN `Material Description` = '3104/3204' THEN `Quantities Reverse`
  WHEN `Material Description` = '3XXX RSI(DROSS)' THEN `Quantities Reverse`
  WHEN `Material Description` = '3003 CLEAR/BARE' THEN `Quantities Reverse` 
  ELSE 0
END)

Alternatively, you can use the IN operator to make a single condition like this:

SUM(CASE
  WHEN Material Description in 
      ('CLASS I 3004 BODY STOCK SCRAP','3104/3204','3XXX RSI(DROSS)','3003 CLEAR/BARE') 
      THEN Quantities Reverse 
  ELSE 0
 END)

MarkSnodgrass

I agree with Michelle's suggestion. I'd also point out that in your original syntax, you are missing multiple END statements. Every time you use CASE, you need to have a matching END. You have 2 CASEs with no ENDs.

MichelleH

@ConstelliumShawn It looks like your first CASE statement was the only one grouped inside the SUM. Rather than using separate CASE statements for each value of Material Description, you can structure your denominator to use a single CASE statement with each condition separated by WHEN… THEN… like this:

SUM(CASE
  WHEN `Material Description` = 'CLASS I 3004 BODY STOCK SCRAP' THEN `Quantities Reverse`
  WHEN `Material Description` = '3104/3204' THEN `Quantities Reverse`
  WHEN `Material Description` = '3XXX RSI(DROSS)' THEN `Quantities Reverse`
  WHEN `Material Description` = '3003 CLEAR/BARE' THEN `Quantities Reverse` 
  ELSE 0
END)

Alternatively, you can use the IN operator to make a single condition like this:

SUM(CASE
  WHEN Material Description in 
      ('CLASS I 3004 BODY STOCK SCRAP','3104/3204','3XXX RSI(DROSS)','3003 CLEAR/BARE') 
      THEN Quantities Reverse 
  ELSE 0
 END)

MarkSnodgrass

I agree with Michelle's suggestion. I'd also point out that in your original syntax, you are missing multiple END statements. Every time you use CASE, you need to have a matching END. You have 2 CASEs with no ENDs.