Ok, I keep having problems with these types of situations.
Let’s say I have 4 columns, where the first two columns are inputs, and the last two columns calculates the percentage of the row amount based on the sum total of the first 2 columns.
count of fruit $ of fruit % of count % of dollar
I want to add a sum to the first two columns, and then calculate the % based on the count & dollars/sum. However, my formula does not work.
Situations where I have to specify the intersection between categories and items always seem to give me trouble. Is there a tutorial on this?
I have attached a example model for clarity. Can someone please take a look? I know that I am missing something simple, but like I said, I always have trouble with this.
Hello TimboQ – Thank you for your post:
At the present time, there is not automatic grouping or collapsing functionality in Quantrix. However, this is a request we have received before, particularly from other Improv users – and I will add your vote to this request in our database.
One shortcut is to right click on a summary item, and choose ‘Collapse Other’ from the menu.
It is always great to have Improv users find Quantrix. Please feel free to post again if other questions arise.
Hi, I used Lotus Improv 10 years ago, and have just discovered Quantrix. I am trying to mount a case for my organisation to adopt it, as it is ideal to what we do.
I use a large number of items, and group them, including a summary(sum). Two things that Improv used to do were to automate this:
When you added a new group, you could have a default which automatically added a group total.
You could collapse a group, except for the group summary item, this enabled you to view the overall structure of your matrix, without the unncessary detail.
I have included a model with two views illustrating what I want: Can I automate this?
The attached model shows two approaches to doing this:
The first is just a “Table View” of Matrix 1 where the Product category tile has been placed in the filter tray and Sum of Products is selected. The last two columns from Matrix 1 are collapsed in the view (Format –> Item –> Collapse, or use the collpase icon in the tool bar).
The second approach uses another matrix “Sum of Products” with both the Region and column categories linked from Matrix 1. The a single formula is used to pull across the Sum of Product data. The last two columns are again collapsed.
Does this do the trick?
Ok, what I want to do right now is bring over just the calculated sum from one matrix to another. In the attached example model, matrix 1 has sum total data for count of sales, revenue, by region and product type. In matrix 3, I would like to just carry over the sum totals.
In the example model, it is possible to do a cut&paste, but the actual model is quite large, and would be impractical. Also, I need to bring over just the sum totals. I can’t just copy over everything and hide the individual cost items.
Please post any suggestions. Thanks.
that was a big help. Sorry, just another quick question. When I put the table of this model into outline mode, it occurred to me that putting in a background color for just the territories (North, South, East, West) would look great, but I don’t know how to select that category without selecting the attached data. Is there a way for me to do this? Tks!
Ok, I have a follow up question.
Please take a look at the attached model. I am trying to take sales data from a sub, dividing it by sales for the total company, and trying to come up with both sum and average. The problem is that my average formula just takes the row percentages and calculates the average, rather taking the dollar weighted average.
Here is what I have:
East $50 $150 33%
West $70 $200 35%
South $45 $400 11%
Summary $165 $750 26%
The percentage average reads 26%, when it should be 22% (165/750). How can I correct this?
Hi There – Thanks for the post:
I think this is a good question – although the answer is very straightforward – it is one that a lot of users have. I will think about training and tutorial techniques for future use.
The answer to your question is in the attached model file. You need to be more specific on what you want to divide by. Once this is done, your formula works.
Please see attached and post again if any questions.