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 (I couldnāt find a āsumā operator)
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.)
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.
(I couldnāt find a āsumā operator)