Desktop productivity for business analysts and programmers

Between (2 percentages) Function in Case Statement with Calculation

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Between (2 percentages) Function in Case Statement with Calculation

Good Afternoon,

 

I am trying to create a case statement where the defined criteria are in percentages and the conditions are in Dollars; given the differences between the criteria and the defined conditions I am running into rounding issues resulting in transactions being left out.

 

For example, I have 3 columns identified as: 

  1. Sale_Beverage
  2. Sale_Food
  3. Total_ Sales 

With this information, I am trying to group the percentage of Beverage and Food sales into the following defined categories:

 

  1. If Total_Sales = $0 then "No Activity"
  2. If (Sale_Food/ Total_Sales) >= 80% then "FoodH"
  3. If (Sale_Food/ Total_Sales) between 55% and 79% then "FoodM"
  4. If (Sale_Food/ Total_Sales) between 45% and 54% the "FoodL"
  5. .....

 

Given the criteria this is what I came up with:

 

CASE
WHEN Total_Sales = 0
THEN "No Activity"
WHEN (Sale_Food/Total_Sales)>= 0.80 -- for 80%
THEN "FoodH"
WHEN (Sale_Food/Total_Sales) BETWEEN 0.55 and 0.79 -- for between 55%  and 79%

THEN "FoodM"

...

END

 

Any help would be gladly appreciated.

 

Thanks,

 

Chris


Accepted Solutions
Solution
‎08-17-2016 05:46 PM
Respected Advisor
Posts: 4,606

Re: Between (2 percentages) Function in Case Statement with Calculation

Use the fact that case clauses are tested in order until one matches. Make sure you cover all possibilities

 

CASE
WHEN Total_Sales = 0
	THEN "No Activity"
WHEN (Sale_Food/Total_Sales) < 0.45
	THEN "Food?"
WHEN (Sale_Food/Total_Sales) < 0.55
	THEN "FoodL"
WHEN (Sale_Food/Total_Sales) < 0.80
	THEN "FoodM"
	ELSE "FoodH"
END as Category
PG

View solution in original post


All Replies
Solution
‎08-17-2016 05:46 PM
Respected Advisor
Posts: 4,606

Re: Between (2 percentages) Function in Case Statement with Calculation

Use the fact that case clauses are tested in order until one matches. Make sure you cover all possibilities

 

CASE
WHEN Total_Sales = 0
	THEN "No Activity"
WHEN (Sale_Food/Total_Sales) < 0.45
	THEN "Food?"
WHEN (Sale_Food/Total_Sales) < 0.55
	THEN "FoodL"
WHEN (Sale_Food/Total_Sales) < 0.80
	THEN "FoodM"
	ELSE "FoodH"
END as Category
PG
Contributor
Posts: 21

Re: Between (2 percentages) Function in Case Statement with Calculation

Thank you PG !

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 245 views
  • 0 likes
  • 2 in conversation