3 Replies Latest reply: Apr 29, 2012 10:59 PM by Barry Sarcich RSS

    Get value from expression rather than expression itself

      Hi,

      While trying to figure out this issue:
      http://community.qlik.com/message/213815

       

      I have been trying to at least get it working on ID level (where the Multiplier_2 field will always be the same). So the idea was that I would make a separate expression where I work out the Multipler_2 value (using Max or Avg) for the Date_From and then use the result of the expression when working out my calculation using Date_To.

       

      My assumption previously was that if you have an expression (let's call it Expression_1) which contains

      =Avg(Value_1)

       

      and you want to use it in a new expression (Expression_2) within the same pivot table which contains

      =Sum(Value_2 * Expression_1)

       

      then Expression_2 will take the result of Expression_1 rather than resolving the expression within Expression_2 to be a combination of the two expressions which would be like this

      =Sum(Value_2 * Avg(Value_1))

       

      But I think that it must be the latter, as when I try and implement this method the expression just fails to resolve and returns Null over all rows.

       

      So does anyone know how I could put Expression_1 into Expression_2 as the result of the expression rather than resolving Expression_1 within Expression_2.

       

      Hopefully this wasn't too confusing :/

        • Get value from expression rather than expression itself
          Daniel Rozental

          Sorry, I don't understand what you're trying to do, perhaps if you would built an example it will help us help you.

            • Re: Get value from expression rather than expression itself

              Hi,

              I have attached my latest version of my test template. I tried something with the AGGR function to get the MULTIPLIER_2 value but somehow it did the calculation but only over 1 row for that ID instead of 3. This is also only ever going to work at ID level anyway, I need to be able to figure out how to do the calculation at row level with the MULTIPLIER_2 based on the ID so that I can use any dimension (eg. country) and still get the correct result for the sum of the calculated values.

               

              So the idea is I have 2 dates selected and I want to get the SUM of:

              VALUE (for Date To)   *   MULTIPLIER_1 (for Date To)   *   MULTIPLIER_2 (for Date From)

               

              MULTIPLIER_2 is always the same for an ID in a particular date, no matter how many rows there are for that ID in that date. So I need to somehow retrieve one of those MULTIPLIER_2 values from Date From and insert it into the sum with the VALUE and MULTIPLIER_1 for Date To.

               

              As stated earlier, in the real world application the MULTIPLIER_2 cannot be multiplied from outside of the SUM() that uses VALUE and MULTIPLIER_1, they must be used inside the same SUM().