Division Calculations where Divisor is Based on Row Characteristic

Hi guys, it’s been a while, hope you’re doing great!

I have a (I believe) complicated question. I have a list of sales of a given Product Category, which has the sales for the Total Category (contains sales of all Brand-Segments), the Total Segments (sales of all brands selling products of that specific segment), and Brand sales (both at Total Category and by Segment). I have values for different types of stores, including Total Stores, and for different regions, including Total Regions.

To illustrate, imagine that I’m talking about Alcoholic Beverages, and I have T Alcoholic Beverages (which is Total Beer + Total Whiskey), Total Beer (Brand A Beer sales + Brand B Beer sales), Total Whiskey (same as previous), and then Total Brand A (Brand A Beer + Whiskey), Brand A Beer, Brand A Whiskey and the same thing for Brand B. I have this data for Total All Stores (Big + Small) and also split into Big Stores and Small Stores. Finally, same data is available for Total All Regions and then split by country.

First problem: I want to create multiple columns which have different types of calculations, but that are basically calculations of importance. For instance, I might want to know the importance of Beer sales that a Region-Story Type has when compared to our Total Region, Total Stores, Total Beer. I want to apply this importance to all product rows within that Region-Store Type that are Beer (so T Beer, Brand A Beer, Brand B Beer), and so it goes. I’m attaching and Excel file with mock-up examples.

Second problem: I’ll be replicating this for different Product Categories. Although my data will still be split by regions, store types, and will have product lines with Total, Segment and Brand data, the names will obviously change (I’ll have different regions, store types, Brands, etc). ideally, I’d like to build something run when using different Product Categories.

I know it is quite complicated, but anyone could help on how I can proceed to do this?

Kindest of regards,
GuilExample.xlsx (14.8 KB)

Hi @g_oliv,

This is a complicated problem, indeed! Take a look at the attached workflow. This is just an example of how it could look like. I didn’t spend too much time, so there might be an easier way to do it.
First, I do some reshaping since the formulas you are using are basically the same in each 9th row. Afterwards, you have to use a Math Formula node for each of those 9 formulas and concatenate the outputs in the end. I was doing it for only the first 3 formulas, just as an example.

I hope this helps you somehow.


Example.knwf (41.3 KB)

Hi @SimonS , Happy New Year!

Sorry for my late response, after a few days without a reply I started to focus on finding an answer on my own and forgot all about the topic :sweat_smile:

I like your solution but as I mentioned the solution would have to be applied to data with ‘fluid’ structure, that is, the formulas won’t necessarily repeat every 9 rows: I’ll have cases that have loads of segments and/or brands, which would require the nodes to be adjusted for each specific case. I could try and transform the row module in a variable, but again this would depend on having the input data following this same repetitive pattern of X rows (which might not be the case). Still, there might be something there and I’ll definitely have a try.

In any case, I’ve already advanced a bit in a solution of my own, it basically involves toying around with loads of variables and rule-based nodes! Might not be the most sophisticated approach, but it seems to be working!

I’ll leave this topic as ‘closed’ for now and keep you guys posted! Thanks a mill for the help!


1 Like

Hi @g_oliv,

Glad you found a solution that works for you! I am happy to help.



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