Configurable Allocation Keys

Hi everyone,

I am working on a reporting tool, where I have to create allocation keys according to revenue share.
For example, a typical allocation key would be (total revenue for a year and a month / total revenue for a year and a month for a profit center).
Then I would apply this key to every single expense item on a table and append the result to the expense item.

I am struggling to make these allocation keys as dynamic and configurable as possible.

I have tried this with Math Formula (but I would need to append way too many interim constant columns to make it workable), also the conditions would apply to string fields (profit center name), which are not seen by Math Formula node.

I tried with multiple Rule-based Row Filters for profit centers, summing up revenue and joining again, but it looks very laborious - there must be something simpler than that.

I tried with Column Expressions but I am not really a JavaScript guy :frowning: (I couldn’t find a “sum” operator)

Any ideas would be more than welcome!

Thanks,
Bogdan

Hi @Bogdan - welcome to the KNIME community!

Could you perhaps post an example of your input data, and what you want to the output to roughly look like? Then maybe someone could help put together an example workflow for you.

(Also, I’m moving this topic to the main Analytics Platform forum for better visibility.)

1 Like

Thanks for the welcome, Scott!

If I were to sum up the calculation of such an allocation key in Excel parlance, it would be:

April Month Allocation Key for X Profit Center= SUMIFS (RevenueColumn, YearColumn, 2019, MonthColumn, ‘April’, ProfitCenterColumn, ‘X’) / SUMIFS (RevenueColumn, YearColumn, 2019, MonthColumn, ‘April’).

I think Column Expressions should fit the bill (since I can combine rules on strings with math computations), but I can’t figure how to do a sum of a column… Feels a bit silly to do average * rowcount.

I would encourage you to post some sample data, even if it is a very small excerpt. That will help people understand what things look like from your end.

On the Column Expressions node - here’s a very helpful blog post from @armingrudd on some common operations you can perform with it: https://blog.statinfer.com/knime-multitasking-with-column-expressions-node/

3 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.