Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

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:

Cal_Dimension.jpg

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

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

Highlighted
whiteline
Honored Contributor II

Re: Set analysis problem

Hi, socura.

You can try this expression:

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

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

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

socura
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 sales

if(GetSelectedCount(Shop)=0,

  Sum(Sales),

  //else give sales according to the logic we defined

  Sum({$<Partner-={$(='"'&GetFieldSelections(Shop,'","')&'"')}>} Sales)

)

Hope it helps!

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

Community Browser