topic Re: sum(<>sum($(formula)) in QlikView

sum(<>sum($(formula))

cedriclupo — Thu, 14 Aug 2014 09:12:20 GMT

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

Re: sum(<>sum($(formula))

tresB — Thu, 14 Aug 2014 09:16:50 GMT

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)

Re: sum(<>sum($(formula))

cedriclupo — Thu, 14 Aug 2014 09:30:34 GMT

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

Re: sum(<>sum($(formula))

tresB — Thu, 14 Aug 2014 10:10:54 GMT

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)

Re: sum(<>sum($(formula))

cedriclupo — Thu, 21 Aug 2014 08:47:31 GMT

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

Re: sum(<>sum($(formula))

tresB — Thu, 21 Aug 2014 08:49:46 GMT

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

Re: Re: sum(<>sum($(formula))

cedriclupo — Thu, 21 Aug 2014 09:24:32 GMT

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