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