14 Replies Latest reply: Mar 12, 2015 10:33 AM by Jonathan Poole RSS

    Expression Help

      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 [right]. 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
          Jonathan Poole

          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([right]) , Amount))

            • Re: Expression Help

              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([right]), Amount)))

               

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

               

              Any other thoughts?

               

              John

                • Re: Expression Help
                  Jonathan Poole

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

                    • Re: Expression Help
                      Jonathan Poole

                      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

                          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
                              Jonathan Poole

                              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

                                  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
                                      Jonathan Poole

                                      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

                                       

                                      Untitled.png

                                       

                                      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.

                                       

                                      Capture.PNG

                                        • Re: Expression Help

                                          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
                                          Jonathan Poole

                                          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

                                          ];

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

                                           

                                          Capture.PNG

                                            • Re: Expression Help
                                              Jonathan Poole

                                              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

                                                  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

                                                    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
                                                        Jonathan Poole

                                                        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.