Solved
Contributor
Posts: 23

# 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
Posts: 5,626

## 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

All Replies
Solution
‎08-17-2016 05:46 PM
Posts: 5,626

## 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: 23

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

Thank you PG !

☑ This topic is solved.