Compounded Summary Table

[#1]

Hi,

If I have this requirement to build a summary table report. As you can see in the sample app, if the user selects key value 6 from the key list, the table to the right will display the weight for that key value (11). What I need to implement is a table that I have simulated in the text object.

Here, when user selects key 6, the color and category list boxes show the corresponding color and category of that selection (blue, and A). What the new summary table should show is two rows:

row 1: blue, and the weight of all data points that are of color blue (regardless of category)

row 2: A, and the weight of all data points that are of category A (regardless of color)

Is this doable in qv?

[jerem1234 · Accepted solution]

Hi Dennis,

Try using the dimension in a straight table:

=Valuelist('$(=color)', '$(=Category)')

With Expression:

=pick(match(Valuelist('$(=color)', '$(=Category)'), '$(=color)', '$(=Category)'), sum({1<color = {'$(=color)'}>}weight), sum({1<Category = {'$(=Category)'}>}weight))

Please find attached. If you plan on having multiple keys selected instead of just one, you'll have to change up the formulas.

Hope this helps!

[#3]

Solution worked great. Can please explain to me what does {'$(=Category)'}  mean in the context of this formula? Is this expand to the current selection value of the Category list box? why do you need the = sign and the single quotes?

[jerem1234]

So the $() will evaluate the expression inside, so when you have only one selected key, what $(=Category) is returning is the value of your Category field for that selected key. So in your example, when you have 6, the Category for that key was A.

     So if you had multiple keys selected or none, you have multiple values for that Category field and $(=Category) will actually return null, since there is no aggregation function to carry out on the multiple values of that field. You'd have to do something like: $(=Concat(chr(34) & Category & chr(34), ',')) to bring back all of the Categories like: 'A', 'B', 'C', ....

What the quotes in '$(=Category)' are doing is just making sure that it interprets that as text, for set analysis.

Hope this helps!