Expression Help

[#1]

I have an expression:

SUM( {1} if(OrderBTL >= 2, if(OrderBTR <= 9, Amount)))

that gives me the exact result that I want, except I would like to replace the constants (2 & 9) with fields from the currently selected set. For 2 I would like to use the field called [left] and for 9 I would like to use the field called

. I have tried a number of different things but have been unable to get it right.

I realize that this expression will only be evaluated once for the sheet,

I am open to suggestions for improvement as well.

Thanks in advance

John

[JonnyPoole]

Do the fields called [left] and [right] have multiple values ?  Sounds like they user would have to select a value from those first ,

The only() function will only evaluate the condition if there is one possible value. it may work for you...

SUM( {1} if(OrderBTL >= only([left])  and OrderBTR <= only(

) , Amount))

[#3]

Thanks for your response. [left] and [right] have a single value based on the active context.

I tried your suggestion:

SUM( {1} if(OrderBTL >= only([left]), if(OrderBTR <= only(

), Amount)))

The expression editor displays "Error in Expression: Nested aggregation not allowed".

Any other thoughts?

John

[JonnyPoole]

Of course ! my bad.  nested aggregations will never work unless with aggr() .  Let me check into an alternative

[JonnyPoole]

This may work better. but i'm not sure if you need a sum(Sales) or a sum( {1} Sales) in the scenario within the IF

SUM( {1} aggr( if(OrderID >= only(left) and OrderID <= only(right), sum(  Sales)),OrderID))

Let me know if you get what you rwant.

[#6]

Thanks for the attempt, but you lost me on how you are using the aggr function. My understanding of aggr() was that it accepts the aggregation function as the first parameter, so I don't really understand what having the if() as the first parameter means.

That being said, I tried a few variations on this:

SUM( {1} aggr( if(OrderBTL >= only(left) and OrderBTR <= only(right), sum( {1} Amount)),OrderNum))

But they all returned no value.

John

[JonnyPoole]

It can work with a conditional expression as long as the result of the expression is an aggregation expression ( sum() in this case).

Not sure why it's not working for you.  Would you like to post the .qvf file ? Likely some subtle differences in my sample and your sample that need some drilling into

[#8]

Jonathan,

I have attached the .qvf file and a .txt file that (I hope) explains the situation.

I appreciate all your help so far. Hopefully we can work this one out!

[JonnyPoole]

The QVF as is seems to work for Bill, both left and right numbers.  When i get down to kate or john though, its summing up the selected names orders as well. For example, when i select kate and left, it gives me johns numbers (good) but also kates numbers (bad).

This tells me the expression is fine but there is something wrong with the associations between the names and the order amounts.

That seems to be proved out below where there is a table that shows the raw associated values coming

If i add left and right as list boxes, i can see that there are actually 2 left and right values ... which one should be used for the calculation ?   I'm not sure but i sense a data model issue. The hierarchy should be able to take care of the correct joining  to the order table at the different nodes , but at a glance i couldn't guess what the rest of the ETL script is attempting to do. I sense you are trying to create a left and right attribute for each node but something is off, its not consistent.

[#10]

Thanks for continuing to investigate this with me and for your patience.

The reason there are multiple left and right values is related to how Qlik builds hierarchies with levels.  An addition filter of Level=1 needs to be applied to get the correct left and right values.

However, I took your thought of there being a data model issue to heart and created another sample app that just uses LOAD INLINE to load the data correctly.

Using this new app I am still unable to get the correct answer without hard coding specific left and right numbers.

Can I inconvenience you one more time to take a look and suggest a solution?

Thanks again,

John