Learning Set Analysis

[sunny_talwar]

Hey guys -

This may be a very easy question, but I never really understood how this would work. While working on a community thread, I was stuck here and didn't know how I would do this. So I created a sample and thought some of the experts out there might be able to help. Lets say I have table like this:

Table:

LOAD * Inline [

POS, ID

A, 1234

B, 1234

C, 1234

A, 1232

C, 1232

A, 1991

B, 1991

B, 1221

C, 1221

];

In the above table, ID 1234 is available in all the POS, but rest of them are only available in some combination of POS, but not all. I am text box where I use an expression =Concat(DISTINCT ID, '|') to show all distinct ID. But now I would like to add a set analysis statement which would show me only IDs which are present in all of my selected POS. So when nothing is selected I should only see 1234, but when I select A and B, I should see 1234 and 1991.

I hope I was able to give a good explanation of my questions above. I am also attaching the sample for ease.

Thanks for your responses.

Best,

Sunny

swuehl‌ MarcoWedel‌

[datanibbler]

Hi sunindia,

I guess that would be a use_case for the AND mode in listboxes. I haven't yet really grasped that concept, so I guess it will be helpful for me as well if I work through your sample which I'm doing now.

I'll be back here when I have found out how to do it.

[swuehl · Accepted solution]

Hi Sunny,

are you looking for something like this? I think I've seen the other thread,too.

edit:

This is the expression

=Concat({<ID = {"=count(distinct POS)=count(TOTAL DISTINCT POS) "}>}DISTINCT ID, '|')

Guess Marco will find more alternative solutions

[datanibbler]

Hi swuehl,

I kind of understand that set_expression - logically, if the COUNT of a field with the dimension active is the same as the Count with the dimension ignored, then that field must be present across all the dimension_values.

I am trying to do this using the AND mode - that should also be possible, no? I mean, then I can select all the IDs which remain available with all the POs I have selected.

But the checkbox is inactive - what do I have to do first to activate this option in a listbox?

Thanks a lot!

[sunny_talwar]

Hahahaha you know what I am talking about because you were the first one to answer on the thread while I was still struggling on it .

I swear to god, I thought there was another way to do this using p() or e(), but I might be mistaken here. This definitely gives the solution I was looking for, but I would love to wait for Marco's alternative solutions .

Thanks Stefan for looking into this one.

Best,

Sunny

[swuehl]

There are some prerequisites your data model needs to fulfill, in this case, a LOAD DISTINCT should be enough.

See attached.

[datanibbler]

Ah, yes, I see. That was in the bog_post by Jennell that I read, but one always needs to hear the important things several times.

Thank you!

Best regards,

DataNibbler

[sunny_talwar]

Huh!! Why is this the first time I am seeing this? I guess it's not used very often. So If I add DISTINCT to my load the selection on A become AND mode? DataNibbler‌ would you be able share the blog you are talking about.

Thanks both of you.

Best,

Sunny

[MarcoWedel]

Hi,

I didn't find a shorter solution than Stefan did.

regards

Marco

[sunny_talwar]

No big deal MarcoWedel‌. Thanks for trying though

Best,

Sunny