topic Re: Set analysis problem in QlikView

Set analysis problem

socura — Wed, 05 Sep 2012 10:01:06 GMT

Hello everybody,

based on the following table

Shop Partner Sales

a 100

a b 50

a c 30

b 500

b a 200

b c 250

c a 150

i'd like to show the sum of the sales but internal sales should be eliminated, based on the selection of shops. That means if shop a and b are selected, just the external sales (without partner) and the sales with partner (shop) c should be displayed.

I tried Sum({$<Partner=E({1<Shop={}>}Partner)>}Sales) but it doesn't work.

Any Idea?

Regards

Mario

Can't anyone help me?

Re: Set analysis problem

Fri, 07 Sep 2012 12:46:10 GMT

I am not sure if I understood your issue completely.

But, if you are looking for the below result:

Instead of using SET Analysis in Expression, keep it as Sum(Sales) but use Calculated Dimension:

=If(Partner = '',Shop,Partner)

Re: Set analysis problem

whiteline — Sat, 08 Sep 2012 14:59:06 GMT

Hi, socura.

You can try this expression:

=Sum({$<Partner={"=not isnull(Partner)"}>} Sales)

Re: Set analysis problem

socura — Mon, 10 Sep 2012 07:56:16 GMT

Hi,

thanks for the answers, but the problem isn't solved. Perhaps I should detail it. The point is to get consolidated results, where the internal sales are eliminated, based on the selection of shops.

If shops a and b are selected:

a 130

b 750

If just shop a is selected:

a 180

Only shop b is selected:

b 950

Only shop c is selected:

c 150

Shops a, b and c are selected:

a 100

b 500

c -

Shops b and c are selected:

b 700

c 150

Maybe set analysis is not the solution. What about triggers..?

Re: Set analysis problem

socura — Mon, 10 Sep 2012 07:56:23 GMT

Hi,

thanks for the answers, but the problem isn't solved. Perhaps I should detail it. The point is to get consolidated results, where the internal sales are eliminated, based on the selection of shops.

If shops a and b are selected:

a 130

b 750

If just shop a is selected:

a 180

Only shop b is selected:

b 950

Only shop c is selected:

c 150

Shops a, b and c are selected:

a 100

b 500

c -

Shops b and c are selected:

b 700

c 150

Maybe set analysis is not the solution. What about triggers..?

Re: Set analysis problem

socura — Mon, 10 Sep 2012 12:19:02 GMT

Hi Roberto,

while posting my answer an error ocurred and I tried to post it again (and again...). After seeing that the posting was repeated, I tried do delete the multiple ones. Unfortunately your posting too. Sorry, this was my first posting in the community...

I stored your solution but if you like to you can repost it.

The solution is almost right. There's just one thing: If I don't select any shop, the external sales are missing. But I could solve this by forcing the user to select at least one shop.

Thank you very much and greetings from cologne!

Mario

Re: Set analysis problem

Mon, 10 Sep 2012 12:42:42 GMT

Hehe don't worry, I just wondered if I had the right answer or not...

I'll repost mine, but let me check the needed finetuning. I'll be back in a few minutes with the QVW again.

Re: Set analysis problem

Mon, 10 Sep 2012 12:49:46 GMT

I think a simple if/else will do the trick. If I understand you correctly, this is the logic

- No shop selected? >> sum(sales)

- shops selected? >>sum of sales with the logic that I already defined...

Then this is the full expression (see qvw attached as well):

=
//when no Shop-selections are made, give sum of sales
if(GetSelectedCount(Shop)=0,
  Sum(Sales),
  //else give sales according to the logic we defined 
  Sum({$<Partner-={$(='"'&GetFieldSelections(Shop,'","')&'"')}>} Sales)
)

Hope it helps!

Re: Set analysis problem

Anonymous — Mon, 10 Sep 2012 13:31:20 GMT

Mario,

Maybe this will work for you:

sum({<Partner-=P(Shop)>} Sales)

Regards,

Michael

Edit: Just tested - looks like working. See attched

Re: Set analysis problem

Mon, 10 Sep 2012 14:06:26 GMT

Nice. I think indeed this results in the same, and is perhaps a "cleaner" solution.

Re: Set analysis problem

Anonymous — Mon, 10 Sep 2012 14:41:07 GMT

Roberto,

Looked at your solution. Yes, logicaly it is exactly the same, I'm just using a "shorter" expression.

Regards,

Michael