Start a new topic

How to create different rounding brackets per country - where the value is lower than the bracket limit (custom rounding up)

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. 

rgds
Kay

xlsx
(12.4 KB)
1 Comment

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. 

Please see the demo file


Login or Signup to post a comment