Using range functions instead of num sum/num count

[marjph]

Hi! We are currently optimizing Qlik Performance and I read one best practice where I need to use range functions instead of the usual num(sum), num (count) function. I found limited examples online for range functions on the front end. 

If I have the ff. as my expression:

(num(count(distinct {<[FIELD1] = {'Yes'}>}[ID FIELD]),vFormatCount0) / num(count(distinct {<[FIELD2] = {'wrapup'}>}[FIELD ID]),vFormatCount0)) * 100

How do I change the expression to one using Range function? 

Also, does this help with the overall UI Calculation/Qlik Optimisation?

Thank you!

[tresesco]

AFAIK, in general, range functions can't replace aggregation functions. I am not sure what made you think that range functions could be used here to tune the performance. Your expression looks good to me (except for the variable you are using - I don't know about).

[marjph]

Hi,

Please see below screenshot. 

[tresesco · Accepted solution]

Numsum() and Num(Sum(.... are NOT same. Similarly for count().

Numsum() is one function and Num(Sum(..   is combination of two functions.

[marcus_sommer]

I agree with Tresesco that the range-functions couldn't replace the aggregations like sum() or count() because they work in a different direction - sum/count will aggregate the items (fieldvalues within a column) vertically and the range-functions do the calculation horizontally like: rangesum(Field1, Field2, ...).

Beside this you could optimize your expression if leave the num() because the result of num() / num() will be again unformatted. Further you could replace your string conditions within the set analysis with a numeric value - this means something like 'Yes' or 'No' with 1 or 0.

But usually you won't notice a significantly performance improvement through these kind of measures - by really large datasets and many objects on the sheet it might be different.

- Marcus

[marjph]

Thank you for clarifying that 🙂

[marjph]

Thank you for taking the time to explain all these! Will consider what you said about replacing the string condition with numeric value 🙂 I'll read more on optimization to see how else can I improve our calculations.