topic Re: Expression Help in App Development

Expression Help

Tue, 17 Feb 2015 15:44:25 GMT

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

Re: Expression Help

JonnyPoole — Thu, 19 Feb 2015 03:06:08 GMT

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))

Re: Expression Help

Thu, 19 Feb 2015 16:35:04 GMT

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

Re: Expression Help

JonnyPoole — Thu, 19 Feb 2015 16:58:52 GMT

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

Re: Expression Help

JonnyPoole — Thu, 19 Feb 2015 17:25:16 GMT

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.

Re: Expression Help

Thu, 19 Feb 2015 23:18:39 GMT

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

Re: Expression Help

JonnyPoole — Tue, 24 Feb 2015 11:41:13 GMT

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

Re: Expression Help

Tue, 24 Feb 2015 19:33:42 GMT

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!

Re: Expression Help

JonnyPoole — Wed, 25 Feb 2015 01:30:50 GMT

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.

Re: Expression Help

Thu, 26 Feb 2015 14:07:52 GMT

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

Re: Expression Help

JonnyPoole — Fri, 27 Feb 2015 16:43:53 GMT

Maybe this is closer:

-----------

PeopleTemp:

HierarchyBelongsTo(PersonKey, ParentKey, Name, AncestorID, AncestorName, DepthDiff) LOAD * INLINE [

PersonKey,ParentKey, Name, ChildBranch

1,,'Bobby'

2,1,'Bill','Left'

3,1,'Peter','Right'

4,2,'Kate','Left'

5,2,'Lloyd','Right'

6,4,'John','Left'

7,3,'Gino','Left'

];

// PersonKey,ParentKey, Name, Branch

People:

Load * , if(PersonKey=AncestorID,'Current Node','Children') as AggregationType

resident PeopleTemp;

drop table PeopleTemp;

Orders:

LOAD * INLINE [

OrderNum, PersonKey, Amount, OrderLeft, OrderRight

1,6,100,4,5

2,6,100,4,5

3,6,100,4,5

4,7,500,11,12

5,3,250,12,13

6,4,150,3,6

];

--------------------

Re: Expression Help

JonnyPoole — Wed, 04 Mar 2015 02:18:35 GMT

Was this of any help ? I liked the post because it brought an angle that was new to me with respect to hierarchies, that is, a hierarchy with just 2 branches (left and right) at each node and how best to reflect that attribute in the hierarchy load. I did feel the expression and load script i presented resolved the situation but the one piece that would need to be customized is to transform the data into a format that i used in my hierarchybelongsto load. anyways , it was interesting so if you get back to this i'd love to hear how things turned out.

Re: Expression Help

Wed, 04 Mar 2015 20:53:59 GMT

I haven’t had a chance to try it out yet, but I will in the next day or so.

I’m glad I was able to challenge you!

Re: Expression Help

Wed, 11 Mar 2015 14:24:57 GMT

Jonathan,

your solution appears to work but for only 1 level down in the tree.

For example, if I choose "Bobby" and "Right" it shows 250, but it should show 750 (Peter's 250 + Gino's 500).

I believe the example works for "Bill" and "Left" because both Kate and John are on the left side. If we were to move John to Kate's right side I bet it will not work.

Thanks again for the attempt. Your approach has given me something else to think about as I work towards a solution.

Re: Expression Help

JonnyPoole — Thu, 12 Mar 2015 14:33:47 GMT

John - i see exaclty what you are referring to. The left/right branch field propogates to all children giving you not only the left branch children but the left branch of those children too (grandchildren) which is wrong for this need.

I'm thinking another attribute is needed to differentiate children vs grandchildren. I'll take another crack at this when i have some cycles.