Get all material IDs of all salesorders containing a specific material ID

[Tino]

Hi Qlik Community,

i'm struggling with the following requirement.

I have a straight table with salesorder-id, material-id and quantity.

The requested feature is to select a material-id and to show which other material-IDs have been sold together with the selected material-ID over all salesorders, the sum of the sold units per material-ID and the appropriate salesorder-id. In the result the selected material-ID should be ignored.

So in other words. Search all salesorders with the selected material-ID, gather the additional material-ids sold with the selected salesorders and show the sum of the quantity.

Any ideas how to achieve this?

Thanks!

Tino

[Channa]

1-$

try this in set analysis

it will remove Selected from All values

[rwunderlich · Accepted solution]

Set Functions P() and E() are your friends here.

Sum(quantity)  // for current material-id selection

Sum({<[material-id]=>}quantity)  // ignoring material-id selection

Sum({<[salesorder-id]=P([salesorder-id]), [material-id]=>}quantity)  //All material ids for all currently associated salesorder-id

Sum({<[salesorder-id]=P([salesorder-id]), [material-id]=E([material-id])>}quantity)  // all currently associatedsalesorder-id, excluding selected material-ids

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Tino]

Thanks @rwunderlich !This was the solution.

Wish you a nice weenend.

BR

Tino