Replacement for a Sum(If...) expression?

[#1]

Hi

I have an expression of the form:

Sum(if(rand()<=vPercentageRecords,'fieldvalue'))

In my app, a user can use a slider to vary a proportion (vPercentageRecords) of records to be summed over; this expression is used in several charts throughout the application. It works fine, but it may be the reason the app runs so slowly. I have learnt that Sum (If..) is inefficient. However I can't find a way to replace it; I have tried Set Analysis, and failed!)

Any help very much appreciated..

Ed

[kouroshkarimi]

Why did set analysis fail? Was it your syntax? Maybe Set Analysis Wizard for QlikView | qlikblog.at can help?

[#3]

Hi Kourosh,

I tried your wizard, and got Sum({$<rand()={"<=$(=vPercentageRecords)"}>} 'field')

The expression is not summing over the records after the rand() selection; I'm shaky on how rand() operates on current selection in this case.

[whiteline]

Hi.

I think you can't convert it with set analysis, at least directly, as the set is calculated once,while your expression is been calculated for each fieldvalue.

Depending on your requirements you can define rand() values in script for each 'fieldvalue'. The user gets almost the same but with the only one random process realization. And set analysis can easily handle this case.

[#5]

As WhiteLine pointed out, set analysis is calculated once,not on a row by row level, so inserting functions into the set will not work.

Try

= - sum(  (rand() <= vPercentageRecords) * FieldValue)

The boolean expression (rand() <= vPercentageRecords) returns a -1 if true, 0 if not so multiplying that by field value will return the sum of -fieldvalue for those that meet the criteria, and 0 if not.

Might not be that much faster, but worth a go?

Also, if this fits how you want your app to work, you could insert a field in the script with random numbers in it, then refer to that field in the set analysis. That would work, but isn't dynamic.

In the script:

Load ...

rand() as randomField,

..

from ...

in the expression:

= sum({<randomField={"<=$(vPercentageRecords"}>} FieldValue)

Regards,

Erica