topic Re: Using range functions instead of num sum/num count in App Development

Using range functions instead of num sum/num count

marjph — Sat, 16 Nov 2024 07:06:21 GMT

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!

Re: Using range functions instead of num sum/num count

tresB — Fri, 07 Dec 2018 05:00:40 GMT

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

Re: Using range functions instead of num sum/num count

marjph — Fri, 07 Dec 2018 07:16:19 GMT

Hi,

Please see below screenshot.

Re: Using range functions instead of num sum/num count

tresB — Fri, 07 Dec 2018 07:35:20 GMT

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

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

Re: Using range functions instead of num sum/num count

marcus_sommer — Fri, 07 Dec 2018 09:04:05 GMT

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

Re: Using range functions instead of num sum/num count

marjph — Fri, 07 Dec 2018 11:38:03 GMT

Thank you for clarifying that 🙂

Re: Using range functions instead of num sum/num count

marjph — Fri, 07 Dec 2018 11:40:34 GMT

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.