topic set analysis question in QlikView

set analysis question

Mon, 23 Nov 2009 22:36:23 GMT

Hi there.

Let's say I have two list boxes: product group, and product.

product group product

group1 product1

group1 product 2

group2 product3

group2 product4 and so on.

What I am trying to do is this:

If I select group1, I want to show product1, product 2 sales, each as percent of group1.

So if proudct1 sale is 100, and product sale is 400, then they would show as 20%, 80% respectively. This part is pretty easy. I can have something like sum(sales)/ sum( total [product group] sales) or something to that effect.

However, I also want the calculation to work when the user selects the products, not proudct group. So for example, if the user select product2 (and nothing selected in product group), I want the chart to show 80%.

What would be the best way to do this?

set analysis question

Mon, 23 Nov 2009 23:13:22 GMT

Something like this:

Sum(Sales)/Sum({1} TOTAL <Group> Sales)

The denominator will get you the total sales per group regardless of selection. QlikView's normal selection functionality should take care of the rest.

set analysis question

Mon, 23 Nov 2009 23:26:12 GMT

I tried it and it does not work. How does the expression know which Group to total over, if I am selecting only from the Product list?

set analysis question

Mon, 23 Nov 2009 23:37:03 GMT

I assumed you had a chart with Groups listed. How would you know which Group it should total over?

set analysis question

Mon, 23 Nov 2009 23:49:33 GMT

Yeah, that is the tricky part I was trying to achieve. What I was trying to figure out how to achieve this if the user select only the product, not the group. I was trying to come up with a set analysis which would figure out which group to sum over, because there is a data relationship between the product and the product group.

set analysis question

Tue, 24 Nov 2009 00:01:17 GMT

Okay I tried it again and somehow it works, even when I do not have the product group selected..... Thanks.