Add a New Argument to the Summary Formula
Whenever I ask a question or make suggestions, I always assume there is a workaround. In this case, I fear the workaround takes too many needless steps.
I’m probably the only user in the Quantrix universe who believes filtered categories should change totals wherever Summary formulae are applied.
How about a compromise for those of us who augment this great tool with Tableau, Power BI, or similar tools, where filtering means ‘to exclude—both the view and in totals’ as opposed to ‘to hide’—the case in Quantrix?
My suggested compromise is to add an argument to the summary formula—a ‘1’ means not to change the total, and a ‘2’ means adjust the total based on what is filtered in the category tray.
Hi Mark,
I do not think there are workarounds needed for this. I very much advocate the “Summaries” or “Total” should NOT be included in the normal data matrices. The model is much cleaner and neater in all formulas if you have the data in one matrix and the aggregation in another.
In this setup, you just need a Filter matrix for the aggregation. I always do it like this, and never felt the need to modify the functionality of the “summary”, since I think it should not be used in the first place.
But this is probably not your preferred answer :-)
Gilbert
With a group of masterminds who have been financial modeling for years, absolutely on the separation of data from calculations from presentation. That mental model applies across many applications.
But in the world of rolling out super-fast models (in my world, we call them HLPs) for high-growth companies, we want to crank out these lite models rapidly. Remember, Quantrix should be 100x faster and better than Excel. Simplifying the ability to filter totals on the fly on just one matrix should be built into the tool, not a cumbersome workaround I’ve had to create that happens to work. How would a developer state this? “It’s not elegant.”
Accordingly, I’m using this part of the forum to express my wish. If this becomes a reality, I will fill the Quantrix tip jar.
Agreed that there are different contexts. IF (for whatever reason) you want to use Summary formulas at all, then I understand your wish for the Excel-like switch to make it behave either way (well I even agree that the summary should sum what you see, not all, but there are maybe people seeing this differently.) – On the other hand, if you build your model with separating the aggregation from the data, I do not think my solution as being a “not elegant workaround”, but rather straightforward. And I want to insist: for complex models, this approach is much easier in the long way then bothering with Summaries. For small quickies, I agree with you, this is different.
I was in a hurry to add the image. Even a young/new Quantrix modeler will notice I didn’t exclude the summary function for the ATS totals column—sorry about that.