Exclude selections in calculated dimension

[#1]

Hi everybody,

I'm using a long expression to calculate the price of complex objects made up of smaller objects (engines are big objects made up of medium objects such as injectors and these are made up of small objcets such as screws):

Sum(Aggr(Sum(small_objects.TOTAL_QUANTITY]*[medium_objects.TOTAL_QUANTITY])*Min(small_objects_cost), small_objects, big_objects))

In my dashboard if I select a small_object I can see the engines that contain this object and with the expression above I can calculate the price of every engine. My problem is that if I select a small object (let us assume it's a screw) I can't see the total engine price anymore, but I just see the price of the small object multiplied by its quantity (this is beacuse every small object has a price, a quantity and the medium objects it belongs to) and so this is my question:

how could I have the sum of the prices of the engines that contains the screw, but without having only the screw selected in the engine (I mean the price calculated as I didn't select only that particular small object)? Now, as I said before, I have a engine with its price, but when I select a small object I only get the price of this object instead of the total price of the engine as before.

So the ideal process would be: the selection of a small object gives me the engine the small object belongs to and then I would like to have the price of each engine calculated with the price of ALL the small objects, not only the one selected.

I thought using {$<small_object=>} was the solution, but this doesn't work and I suppose this is beacuse of the aggregation

Thanks in advance for your help and your attention

Best regards

Nicolò Cogno

[#2]

swuehl‌ stalwar1‌ gwassenaar‌

Does anybody know how to solve that?

I'm sorry, I don't want to spam, but I'm really struggling with this

[sunny_talwar · Accepted solution]

May be this?

Sum({<small_objects, Engine = p(Engine)>}Aggr(Sum({<small_objects, Engine = p(Engine)>}small_objects.TOTAL_QUANTITY]*[medium_objects.TOTAL_QUANTITY])*Min({<small_objects, Engine = p(Engine)>}small_objects_cost), small_objects, big_objects))

[#4]

First of all thanks for your help as always!!!

I used your expression and now I get values, but they're still too small

Is there something I could tell you to help you giving me the expression?

Thanks again a lot!!!!

[sunny_talwar]

I guess I don't understand if this set analysis is needed for all parts or just one part of the expression? Do you need this for

Sum(small_objects.TOTAL_QUANTITY]*[medium_objects.TOTAL_QUANTITY])

or

Min(small_objects_cost)

or both?

Easy way to see what this is doing is to create a straight table with your aggr() dimension as dimensions. So, like this

small_objects

big_objects

and this as your expression

Sum(small_objects.TOTAL_QUANTITY]*[medium_objects.TOTAL_QUANTITY])

Min(small_objects_cost)

and see which part of the expression is not behaving the way you would expect and then go from there.

[#6]

Thanks again! I need set analysis for both

Sum(small_objects.TOTAL_QUANTITY]*[medium_objects.TOTAL_QUANTITY])

and

Min(small_objects_cost)

I'am veryfing everything with the straight table and now I will also verify those expression!

I understood the expression you gave me and it seems that QS doesn't recognize the P() function, it has no color, is this normal?

[sunny_talwar]

Just to make sure, you do have a field called Engine, right?

[#8]

Yes, I've got it and QS recognize everything, the expression works, but it's not still completely right

[sunny_talwar]

What is not working?

[#10]

I mean I get a result, but it's not correct, it's smaller than it should be.

Maybe the problem is that set analysis is not being applied to both the members of this product:

[small_objects.TOTAL_QUANTITY]*[medium_objects.TOTAL_QUANTITY]

Is that possible?