topic Get value from expression rather than expression itself in QlikView

Get value from expression rather than expression itself

Fri, 27 Apr 2012 02:44:10 GMT

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

danielrozental — Sun, 29 Apr 2012 23:39:37 GMT

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

Mon, 30 Apr 2012 02:25:31 GMT

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

Re: Get value from expression rather than expression itself

Mon, 30 Apr 2012 02:59:34 GMT

Ok, I have figured out how to get it to work on ID level aggregation (by putting NODISTINCT in the AGGR() function). Still trying to figure out how to do this on a higher aggregation level.