Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cedriclupo
Partner - Contributor III
Partner - Contributor III

sum(<>sum($(formula))

hello Everyone,

you are my ast hope to find a solution to my problem that I will expose. thank you in advance for all the help.

I have a dimension "PART NUMBER" which contains a related part (Npart_related) - which is also a part number.

For one part, i have to calculate the BIP (a formula) of the RPART.

This formula looks like :

<CODE>

sum({<[Part Number]={"$(=Npart_related)"},iprimeth={12}>}distinct [Part Price])

*
sum(distinct {<[Part Number]={"$(=Npart_related)"},[Sort-nbr]={50},[iptyp-to-txt]={'BIP'},iprimeth={12}>}rFact)

</CODE>

We can see here that a set analysis was made ( {<[Part Number]={"$(=Npart_related)"}) and the calculation works ok. My problem with this solution is that I have to select a part number. I do not want that.

I wish to do a table containing "PART and BIP of related part.

I have tried to sum({<[Part Number]={"$(=Npart_related)"} aggr($(formula),[Part Number])) but as the formula containssum() function, it created a nested expression error.

Does any of you have an idea?

Thank you a lot

ced

6 Replies
tresesco
MVP
MVP

Try like:

sum({<[Part Number]=P(Npart_related),iprimeth={12}>}distinct [Part Price])

* sum(distinct {<[Part Number=P(Npart_related),[Sort-nbr]={50},[iptyp-to-txt]={'BIP'},iprimeth={12}>}rFact)

cedriclupo
Partner - Contributor III
Partner - Contributor III
Author

Indeed that gives an answer, but it gives me the solution over the parts itself, and not the related.

PART -> Related

67010tl2 -> -

67010TL0 ->-

67050TL2 -> 67010tl2

67050TL0 -> 67010TL0

Capture.PNG.png

tresesco
MVP
MVP

Not sure if understood right. Try:

sum({<[Part Number]=P({1<[Part Number]={"=Npart_related"}>}[Part Number]),iprimeth={12}>}distinct [Part Price])

* sum(distinct {<[Part Number]=P({1<[Part Number]={"=Npart_related"}>}[Part Number]),[Sort-nbr]={50},[iptyp-to-txt]={'BIP'},iprimeth={12}>}rFact)

cedriclupo
Partner - Contributor III
Partner - Contributor III
Author

hey there,

thank you for your answer.

This is correct, but it brings 2 problems:

->If few parts selected, it sums the result over ALL the parts

->To have the correct number, we must select ONE part

I wish to find a solution so that it displays part by part the value of the related part. I am wondering if it's something possible.

Thanks  a lot for your answers, it's very helpful

Regards

tresesco
MVP
MVP

Could you post a sample qvw and explain the expected output against that sample data there?

cedriclupo
Partner - Contributor III
Partner - Contributor III
Author

hey, here is the attachment. I made 2 text boxes  to explain the output.

Each part has a BIP

One part has a related part. BIP2 is the BIP of the related part

if one part doesn't have a related part, then BIP2 is 0

Part / Related / BIP / BIP2

A      /  Z           /  5  /      10

B      / Y           / 6   /      11

Y      /  -           / 11     /      -

Z      /  -           / 10      /      -

I hope i was clear enough.

Thank you