Calculating with Summary Item

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Answers
Questions
2.75K viewsFormulas and Functions
0

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
12 25
15 30
25 40

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.

Thanks.

0

TimboQ,

We recently released Quantrix 2.2, and it has the ‘linking existing categories’ function you desired. More Details Here:

[url:2wc72p45]http://www.quantrix.com/forums/showthread.php?t=273[/url:2wc72p45]

Thanks for the feedback!

-Mike

0

Its not yet possible to establish a link between 2 existing identical categories. Others have expressed this same improvement and I have added your vote to this request in our database.

-Mike

0

Thanks Mike,

If I control click and drag a category, then I can have a common category in two different matrixes. However, if I create identical categories in two different matrixes, is it possible to link them?

0

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.

-Mike

0

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?

Attachments

Query.model
0

Wow, that did it. Thank you for your help!

The problem I had was that the column category was not linked; so the values from the first column kept repeating itself. Now, with the column linking, everything is working as expected.

0

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?

Chris

0

My last question was a bit ambiguous. In matrix 3, I would like to bring over the sum totals for the first 4 columns from matrix 1. Thanks!

0

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.

0

Sure – hold the CTRL key down while clicking on the item
-or-

Select the item, and choose Edit > Select Items Only from the menu.

-Mike

0

Thanks Mike,

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!

0

Hello Numberpro,

I think you need to set up 2 summary items per product category item. One for the Sum and One for the average – then you can write the appropriate formulas with skips.

Easiest to demonstrate in the attached file. Post again if any questions.

-Mike

0

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.

For example:

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?

Thanks!

0

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.

-Mike

Latest Questions