evaluate variable in set analysis

[#1]

I have a pivit table where

the expression is:

sum( {$<varX = {$(=calc)}>} xyz )

calc:

max( varQ)

problem :

{$(=calc)} evaluates to {max( varQ)} and no value.

if i make calc with an equalsign : =max( varQ) there is a value but its onyl calculated once = the same for all rows in the table.

what to do?

[johnw]

A set is ALWAYS only calculated once for all rows in the table. So while the dollar sign expansion has the same effect, even if you fixed that part of it, I believe like this...

sum({<varX={$(=$(calc))}>} xyz)

...it wouldn't change the results, because the max(varQ) will STILL be calculated for the entire table instead of that row since it is used in set analysis. So I suspect you're going to need to abandon set analysis and do what you want with a sum(if()), which IS sensitive to the row it is on, as long as you stick with max(varQ) as the value for calc instead of =max(varQ). So I think you need this:

sum(if(varX=$(calc),xyz))

[#3]

well then sum(if(varX=$(calc),xyz)) will just turn into sum(if(varX= max(varQ) ,xyz)) so the max(varQ) is not calculated..

[#4]

Hi,

I'm not sure about this, but you can try to change calc with max($(varQ)) , I'm guessing varQ is another variable, so maybe this can help

Regards!

[johnw]

calthabis wrote:well then sum(if(varX=$(calc),xyz)) will just turn into sum(if(varX= max(varQ) ,xyz)) so the max(varQ) is not calculated..

You're half right. It will turn it into sum(if(varX=max(varQ),xyz)). Then it will calculate it.

[#6]

Doing that way makes the expression return null.

The only way I can get a result is writing: sum( if( varX = $(=max(varQ) ) ) ) but that evaluates for the whole table.

[johnw]

calthabis wrote:Doing that way makes the expression return null.

Ah, so it does.

Variables do get calculated after replacement, though, so that's not the problem. It appears that the problem is that max(varQ) is meaningless given where it is in the expression. The if() is processing row by row through your source table. So inside of the if(), there's no aggregation occurring, so aggregation functions probably don't work and return null.

So the trick is getting it to not aggregate varX, but to aggregate varQ by your dimensions. One solution is to use sum(aggr(if())), where you aggregate by the dimensions plus a row ID, then use max(total <your dimensions here> varQ) to ignore the row ID again, but only for the aggregation function. So if your pivot table dimension is field "something", and you have a unique row ID of "ID", you could do this:

calc: max(total <something> varQ)
dimension: something
expression: sum(aggr(if(varX=max(total <something> varQ),xyz),something,ID))

See attached example. I think it's doing what you're asking for. Not sure how easily it is applied to your real application, though. And there's probably a simpler expression that would do the same thing.