Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Compounded Summary Table

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?

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

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!

View solution in original post

3 Replies
jerem1234
Specialist II
Specialist II

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!

Not applicable
Author

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
Specialist II
Specialist II

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!