sum(summary()) for groups with varying number of items
I have a matrix with groups of items for which I want to do sums. The groups have different numbers of items, so a category won’t work.
Currently, I create a single forumula for each group. Grp-Yr1.Total = sum(summary(Grp-Yr1)), Grp-Yr2.Total = sum(summary(Grp-Yr2)) etc. Overtime the number of formulas has increased as I’ve added new Groups of items. I’ve thought there should be a way to do this with a single formula. Well, this weekend I tried to do that, without success.
There’s probably a simple way to do this but I couldn’t find it. Does anyone have an answer.
Thanks in advance
The system wouldn’t allow my model to be uploaded so here’s an image of the matrix.
Hi lyledp, one way to solve this problem would be adding a new item to calculate cumulative values for each group with a single formula. Then you will not need sum summary items and formulas at all. Let me know if it would work for you, I can try to model it for you.
Hi lyledp,
try that scalable model structure, plz.
*
*
where
M1:: flat table (your source data);
M3:: year allocation using the constrain input by means of Ctg M2::Years;
M2:: sum report with a single formula.
Good luck
Lyndsey, thanks for the quick response. Not the answer I was hoping for, but not unexpected either.
I had thought @level() would work. @level(B,1) retrieves the correct group name and I thought that could be used in the formula. But when used within the formula on the right side it throws a general syntax error that doesn’t identify the location in the formula of the error. When used on the left side there’s no error but it doesn’t produce any results either.
I’ve enclosed an image of a simpler model showing these attempts.
Hi lyledp, unfortunately, there’s not a straightforward way to do this. You could write a script that created the formulae for you, but not one that would help consolidate them.
It sounds like you’re against it, but you could use a category anyway and allow it to be sparse – just hide the empties.
(PS: We’ve made this a sum(summary()) activity a little easier in the newer version of Quantrix with the introduction of a Summarize functionality, which allows you to hover over the group(s) and click an arrow to automatically add sum(summary()) formulas, either for single groups or across all groups existing in the category. It also serves as a fast expand/collapse functionality. Ctrl + click enables category-wide summarization (add summaries everywhere where it makes sense if they don’t already exist).
Lyle – Right, I see what you are trying to do – unfortunately as you know, @level() and other functions (outside of recursion) don’t work on the left side of the formula. We have gotten this request in the past, though – I’ll be sure to echo it again to the product team. I’m sorry I don’t have a better answer for you! – Lyndsey