Hello All, I have some raw data where I'm rounding the price to the nearest hundreth - rounding to the nearest tenth is not accurate enough. I have similar prices that should be grouped to the same price level. For example, in my data below, $13.60 and $13.63 should be grouped to the $13.62 price level with the corresponding new totals: Before: BRANDPRICEQTY% of BusinessSalesOGIO13.6518% $ 68.00OGIO13.622071% $ 272.40OGIO13.63311% $ 40.89 After: BRANDPRICEQTY% of BusinessSalesOGIO13.6228100% $ 381.36 I would know the correct price level to use by taking the level with the highest % of business. Any ideas on how to group these other price levels into the correct one would be very appreciated. Thanks!

Using IF Statement to Group Rounding Issues

Hello All,

I have some raw data where I'm rounding the price to the nearest hundreth - rounding to the nearest tenth is not accurate enough. I have similar prices that should be grouped to the same price level. For example, in my data below, $13.60 and $13.63 should be grouped to the $13.62 price level with the corresponding new totals:

Before:

BRAND	PRICE	QTY	% of Business	Sales
OGIO	13.6	5	18%	$ 68.00
OGIO	13.62	20	71%	$ 272.40
OGIO	13.63	3	11%	$ 40.89

After:

BRAND	PRICE	QTY	% of Business	Sales
OGIO	13.62	28	100%	$ 381.36

I would know the correct price level to use by taking the level with the highest % of business. Any ideas on how to group these other price levels into the correct one would be very appreciated.

Thanks!

Common Use Cases

Accepted answers

Joe_Mako

Building from Rod's description, how about the attached?

Sort - ensure records are in the order needed
Multi-Row Formula - identify records that are first, meaning greatest "% of Business" value
Sample - to select just the first record
Join - to bring the price info from first record to all all other records in BRAND
Filter - split stream into record that can be aggregated together (that meet the condition), and those that do not
Formula - recalculate the Sales value
Summarize - aggregate the data
Union - bring all records together

grouping.yxmd

All comments

bwortham32

Maybe I should clarify.. In the end, I want to say "IF the price level is less than 20% of business AND +/- $0.15 cents from the correct price level of $13.62, group it with $13.62."

RodL

If I'm understanding your use case correctly...

you would identify the "standard" price with a Summarize tool (grouped by the Brand I assume?) and find the Max % of Business
follow with a Formula tool to calculate the boundaries of the +/- 15 cents
join back to original data flow on Brand and from the unmatched data anchor, attach a Filter that filters on the <20% value and whether the price falls within the +/- range
From the True side of the Filter, join back to the Summarize to and assign the price from that.

I think that conceptually would get you what you are looking for.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703