calculate over a Category, always skip “self”
Hello all – another “new user perspective” question / proposal. Would love your feedback.
I fell in love with Quantrix immediately, especially the category linking and natural language formulas.
For my very first formula I wanted to sum over a list of Product 1… items within the “Products” category, putting the answer in another item called “Total”, at the bottom of that item list. To do this I first attempted sum(Products), thinking that this “feels” like how Quantrix should work. But of course this generates a “circular references” error, because Total is in fact part of the Products category. I then tried sum(Product) skip Total, but this doesn’t work (no error) because I guess skip applies to the left side of the formula and so skips the vital job of actually putting the total in that cell…
I know there are multiple ways to accomplish this core basic job in Quantrix. I can sum over a selected range or create a group and sum over the group, and I know I could use sum(summary()). But I keep coming back to the simplest syntax thinking it should work.
Here’s a thought – could the sum([Category]) item always ignore itself but compute over all other items in the category? One would still need to skip subtotals or other items desired to be skipped, but the simple default would just work.
Thoughts?
Hey Rich, thanks for the feedback. I can see that you have created a matrix with a different Totals and Groups that you compute over using different configurations of the skip function. Helpful! BTW I’m not in any way advocating for lesser syntax 🙂 and the great online manual I think covers sum(summary())
I don’t actually understand what is happening in your “Proposed with skips” column. Where does the 153 come from?
What I think learned from your example is that sum(summary()) skips itself and *specifically* other sum(summary()) type items. But, and this isn’t shown in your example, it doesn’t skip other computations (which, not skipping is what we want) like:
That’s what I meant by “One would still need to skip subtotals or other items desired to be skipped”, which I think is the behavior of sum(summary()). – you still have to specify to skip things you want to skip.
By the way, I sort of missed the forest for the trees in that sum(Products) *is actually doing what I want* i.e. it is skipping itself. It’s just that it’s giving a warning… which in retrospect I think is totally righteous.
So, I’ll revise the suggestion and just ask for more detail on why we can’t do “Total = sum(Products) skip Total” and get the correct result with no warning.