# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor

## Set analysis problem

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?

Tags (3)
10 Replies
Not applicable

## Re: Set analysis problem

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)

Honored Contributor II

## Re: Set analysis problem

Hi, socura.

You can try this expression:

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

New Contributor

## Re: Set analysis problem

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..?

Highlighted
New Contributor

## Re: Set analysis problem

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..?

New Contributor

## Re: Set analysis problem

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

Not applicable

## Re: Set analysis problem

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.

Not applicable

## Re: Set analysis problem

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 salesif(GetSelectedCount(Shop)=0,  Sum(Sales),  //else give sales according to the logic we defined   Sum({\$<Partner-={\$(='"'&GetFieldSelections(Shop,'","')&'"')}>} Sales))`

Hope it helps!

Esteemed Contributor III

## Re: Set analysis problem

Mario,

Maybe this will work for you:

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

Regards,

Michael

Edit: Just tested - looks like working.  See attched

Not applicable

## Re: Set analysis problem

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