How to create different rounding brackets per country - where the value is lower than the bracket limit (custom rounding up)
K
Kay Fontijn
started a topic
5 months ago
Hi team,
I've got perhaps a somewhat unusual question.
I'm trying to roundup values to a specific set of values specified in a secondary file. I can do it by writing a calculation like
If [value field] <= 50, then 50
if [ value field] <= 100, then 100 etc.
However, in different countries, and across different years these brackets may be different. So I'd like a sustainable solution that relies on actual input data as to what brackets exist, instead of hard coding these in a calculation.
How would you solve this in omniscope?
As example I've added an example set of 5 records with a KG value that requires grosses up,
The specific weight brackets are the right of the example records.
Here is one solution - it requires an Inequality join block (with partition on Country) - then you can join the invoice kg value with all the bracket values that are equal or higher.
(partition will ensure this is done on a per country basis)
Inequality block is a Custom block and it will require R installation, if you haven't done that already. If not - instructions are on the block's Setup tab.
Follow this with an Aggregate block, to pick the lowest Bracket value per invoice.
Kay Fontijn
I can do it by writing a calculation like
So I'd like a sustainable solution that relies on actual input data as to what brackets exist, instead of hard coding these in a calculation.
Kay