Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

When is it best to use sum(if()) over Set Analysis in an expression?

When is it best to use sum(if()) (or count() etc...) over Set Analysis in an object expression?  Is there an advantage of one over the other as it relates to process speed? 

1 Solution

Accepted Solutions
Henric_Cronström

Often the Set analysis is faster than the Sum(If(...)). However, to make a fair comparison you must compare five different cases:

  1. Sum(if(IsThisYear='True' , Sales))
    The string comparison "IsThisYear='True'" is CPU-expensive. Avoid this construction!
  2. Sum(if(Year=2013 , Sales))
    A numeric comparison is a lot faster than a string comparison. But still slower than a booloean.
  3. Sum(IsThisYear * Sales)
    A multiplication works fine if the aggregation is a Sum. It does not work with Avg or Count. And multiplication is not very fast.
  4. Sum(if(IsThisYear , Sales))
    Using the "IsThisYear" as a boolean is faster than using it in a relational comparison.
  5. Sum({$<IsThisYear={1}>} Sales)
    Set analysis is often the fastest way.

My view is that alternative 4 or 5 are acceptably fast, but the three first constructions should be avoided.

HIC

View solution in original post

4 Replies
kaushiknsolanki

Hi,

Kindly go though the attached file for clarification on each of the expression.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Henric_Cronström

Often the Set analysis is faster than the Sum(If(...)). However, to make a fair comparison you must compare five different cases:

  1. Sum(if(IsThisYear='True' , Sales))
    The string comparison "IsThisYear='True'" is CPU-expensive. Avoid this construction!
  2. Sum(if(Year=2013 , Sales))
    A numeric comparison is a lot faster than a string comparison. But still slower than a booloean.
  3. Sum(IsThisYear * Sales)
    A multiplication works fine if the aggregation is a Sum. It does not work with Avg or Count. And multiplication is not very fast.
  4. Sum(if(IsThisYear , Sales))
    Using the "IsThisYear" as a boolean is faster than using it in a relational comparison.
  5. Sum({$<IsThisYear={1}>} Sales)
    Set analysis is often the fastest way.

My view is that alternative 4 or 5 are acceptably fast, but the three first constructions should be avoided.

HIC

View solution in original post

Not applicable
Author

Yes set analysis is the fastest way.

Not applicable
Author

Thanks

This was really very helpful for me!

Regards,

Kuldeep