How to select or exclude values based on other fields in set expression?

[#1]

Hi,

I want my expression to have only set expression for speed and memory (use a lot of data...)

But I can't figger out how to do the following in a set expression, I never get the right answer when my pivot is expanded and collapsed... Any help?

AVG({$<Field1={1,2,3}, Field2={4}>} if(Field5=Field6,'',Field5))

I thought something like this:

AVG({$<Field1={1,2,3}, Field2={4}, Field5-=Field6>} Field5)

But I tried a lot already...

Thanks in advance!

Regards,

Bram

[Anonymous]

Hi Bram,

Try something like this:

avg({$<Field1={1,2,3}, Field2={4},Field5={$(=only(Field6))}>} Field5)

Jonathan

[#3]

Thanks for your answers, but now it still takes values when Field5=Field6... The If doesn't do this...

See screenshot, actual expression in screenshot is:

AVG({$<Field2={AA}, Field3={1}>} if(Field5=Field6,'',Field1))

As you can see only the if statement gets the right results...

Second expression is: AVG({$<Field2={AA}, Field3={1}, Field5-={$(=only(Field6))}>} Field1)

Third: AVG({$<Field2={AA}, Field3={1}, Field5-={"Field6"}>} Field1)

[#4]

No one?

[jonathandienst]

Bram

The set expression is evaluated once, ouside of the chart, and has no knowledge of the chart dimensions. In this case, this means that it does not know which value of Field6 it must compare to Field5. You original Sum(If()) expression is evaluated inside the chart and is probably the correct solution for your problem.

If you want to ensure that Field5 is equal to ANY possible value (based on selections) of Field6, then you could try

     =AVG({$<Field1={1,2,3}, Field2={4}, Field5=P(Field6)>} Field5)

But I suspect that this is not correct for your requirements. If Sum(If()) gives the correct result and the set expression above does not, then you may want to add a flag field in your load script, like this:

LOAD ...

     Field5,

     Field6,

     If(Field5 = Field6, 1, 0) As Field56Flag,

     ...

And then use the flag in your set expression, like this:

    =AVG({$<Field1={1,2,3}, Field2={4}, Field56Flag={1}>} Field5)

Hope that helps

Jonathan

[#6]

Hi Jonathan,

Thanks for the help, then I guess I'll stick to the if() for now, field5 and field6 are not in the same table, so I would have to join them first to do a comparison in the load script...

If I run in to too many 'object ran out of memory' I'll change my load script 🙂

Thanks!

[jonathandienst]

Bram

The fact that they are in different tables expains why QV takes memory and time to calculate the expression. It has to do an in-momory join to complete the calculation and this can be resource intensive.

Regards

Jonathan