Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marjph
Contributor II
Contributor II

Using range functions instead of num sum/num count

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!

Labels (2)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

 

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

View solution in original post

6 Replies
tresesco
MVP
MVP

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
Contributor II
Contributor II
Author

Hi,

Please see below screenshot. 

range.JPG

tresesco
MVP
MVP

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
Contributor II
Contributor II
Author

Thank you for clarifying that 🙂
marjph
Contributor II
Contributor II
Author

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.