Set analysis problem

[socura]

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?

[#2]

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)

[whiteline]

Hi, socura.

You can try this expression:

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

[socura]

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]

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]

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

[#7]

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.

[#8]

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!

[Anonymous]

Mario,

Maybe this will work for you:

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

Regards,

Michael

Edit: Just tested - looks like working.  See attched

[#10]

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