Inverse data aggregation


Hello, Community!

Let imagine I have a table like this, and view of it:

As you see, I can view number in two boxes based on type. So, how can I show inverse graph, where horizontal axis will be Box 1 – Box 2, and each box will have two bars with Type = Fruit and Type = Vegetable? Note that there are 16 vegetables in box 1, not 2.

Link to table to play with.


Can’t you do this by changing the axis of the graph in the display button?


No, because there is no single column “what box”. One axis – one column.


I don’t think you can do this without changing your table so that you have a column “Which box” you could segment on, and then a column “Box value”, like this:

P.S. If you don’t want to change your table, you can create a new table and populate it from your original table in this fashion using some formula magic (if row is odd then use value this, else that…) and then base your chart on that.


Here’s the solution with derived table

UPD: embed demo seems to not assign row IDs to new rows. In doc will actually work.

Would you be interested in a part-time gig, plugging in the formulas to my doc?

Wow, cool, thanks! I’ve thought about derived table, but have forgotten about actions and triggers to populate it automatically.