topic Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF in SAS Enterprise Guide

Enterprise Guide Computed Column: Conditional Sum aka SumIF

Fistful_Dollars — Tue, 06 Feb 2018 22:54:52 GMT

I would like to sum a column, "Sales", with the following conditions:

Sales amounts does not equal amount in "Returns" column.
Sales amounts does not equal amounts in "Rebates" column.

Can I do this with a Computed Column in Enterprise Guide? If not, please help with code. Thank you.

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Reeza — Tue, 06 Feb 2018 23:05:14 GMT

@Fistful_Dollars wrote:

Can I do this with a Computed Column in Enterprise Guide? If not, please help with code. Thank you.

Yes you can use computed columns for this.

Create two columns using CASE statements, one for condition #1 and one for condition #2.

Then sum each of those new columns.

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

kiranv_ — Wed, 07 Feb 2018 01:37:55 GMT

just expanding on what @Reeza suggested

sum( case when sales_amount ne Returns then sales_amount else 0 end) as computed_column1,

sum( case when sales_amount ne Rebates then sales_amount else 0 end) as computed_column2

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Reeza — Wed, 07 Feb 2018 03:24:38 GMT

@kiranv_One of those should be eq not ne 🙂

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

FredrikE — Wed, 07 Feb 2018 11:20:06 GMT

Why use two columns?

Why not just add this condition in the computed colums editor and then select aggregation sum?

case

when (Sales_amounts ne Returns OR Sales_amounts ne Rebates) then sales_amount)

else 0

end

Depending on the logic use AND instead of OR...

//Fredrik

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Reeza — Wed, 07 Feb 2018 15:42:56 GMT

@FredrikE is correct, I was reading that as creating two different sums. If it's a single sum you can definitely use the OR condition within the CASE statement.

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

kiranv_ — Wed, 07 Feb 2018 16:41:57 GMT

@Reeza you are right, looks like I did not understand the question properly

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Fistful_Dollars — Wed, 07 Feb 2018 16:54:14 GMT

Thank you all for the great help.

I ended up using something similar to FredrikE, but with an "OR". I also added another WHEN statement I needed.

CASE
     WHEN ((Return_Amt + Store_Credit) = Sale_Amt)
          THEN Return_Amt
     WHEN Sale_Amt ne Return_Amt AND Sale_Amt ne Rebate_Amt
          THEN Sale_Amt
ELSE 0 END

Then I sum the column in the next query.

For some reason when I put the first "WHEN" statement with the formula below the other, the Sale_Amt would populate, not the Return_Amt. I suppose the first "THEN" takes precedence on the CASE statement. Thank you, everyone.

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

FredrikE — Fri, 09 Feb 2018 07:21:07 GMT

As soon as a case/when evaluation is true it stops the evaluation, so more important "when"'s should come first 🙂

//Fredrik