Desktop productivity for business analysts and programmers

Enterprise Guide Computed Column: Conditional Sum aka SumIF

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Enterprise Guide Computed Column: Conditional Sum aka SumIF

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.


Accepted Solutions
Solution
‎02-07-2018 11:53 AM
Occasional Contributor
Posts: 13

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

[ Edited ]

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.

View solution in original post


All Replies
Super User
Posts: 22,850

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Posted in reply to Fistful_Dollars

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. 

 

 

 

 

PROC Star
Posts: 499

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Posted in reply to Fistful_Dollars

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

Super User
Posts: 22,850

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

@kiranv_One of those should be eq not ne Smiley Happy

Super Contributor
Posts: 341

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

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

Super User
Posts: 22,850

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

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

Solution
‎02-07-2018 11:53 AM
Occasional Contributor
Posts: 13

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

[ Edited ]

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.

Super Contributor
Posts: 341

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

Posted in reply to Fistful_Dollars

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

//Fredrik

PROC Star
Posts: 499

Re: Enterprise Guide Computed Column: Conditional Sum aka SumIF

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 318 views
  • 2 likes
  • 4 in conversation