Enterprise Guide Computed Column: Conditional Sum aka SumIF

[Fistful_Dollars]

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

1. Sales amounts does not equal amount in "Returns" column.
2. 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.

[Reeza]

---

@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. 

[kiranv_]

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

[Reeza]

@kiranv_One of those should be eq not ne 🙂

[FredrikE]

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

[Reeza]

@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.

[Fistful_Dollars · Accepted solution]

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.

[FredrikE]

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

//Fredrik

[kiranv_]

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