Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ericarru
Contributor

What is Faster, a Sum or a Max

Hello, I have a question about optimisation.

Suppose that my table have binary data on a dimension (0 or 1) named "IsConnected" that is telling that if someday a product is or is (1) not (0) connected, every day. If you select a period of some days (maybe it depends on the size of days you select, let's suppose that is huge) what is better to do in order to improve the efficiency of the formula:

  1. Sum(IsConnected)
  2. Max(IsConnected)

Since it is a relational DB, I think it is easier to do a Max because QlikView have a Inner Table with the different values of the dimensions so it has only to check between two values.

The second question (less important for me now) and maybe more tricky is: What if the variable is not binary?

For example: Suppose that we have the number of Connected Hours of a product, in one column we have the accumualated of the connected ours ("Accum_Conected_Hours") and on the other the ours that this product has been connected this day (Daily_Conected_Hours) . What it's better in order to calculate the total hours that the product has been connected on a given period:

  1. Sum(Daily_Conected_Hours)
  2. Max(Accum_Conected_Hours) - Min (Accum_Conected_Hours) *

* I am aware that doing a max - min may be the result is not the same but it can be fixed with a set analysis.

Dummy data:

    

DayIsConectedDaily_Conected_HoursAccum_Conected_Hours
111010
21515
31823
40023
51427
60027
71532
81840
911050
101555
110055
Tags (1)
3 Replies
MVP
MVP

Re: What is Faster, a Sum or a Max

These questions are best answered by testing on your data. It is hard to make predictions without those tests as it depends on a while lot of factors. I would expect the Max and the Sum to take a similar amount of time - but I don't see how Max() will answer the first question for you.

For the second question, I would expect the second expression to be a little faster, but again you would need to test and the difference may be negligible.

ericarru
Contributor

Re: What is Faster, a Sum or a Max

Thank You jontydkpi‌, is there a way to check this by myself curately?

MVP
MVP

Re: What is Faster, a Sum or a Max

You can check the execution times of objects (eg tables or charts), but not the individual expressions. So create two tables using the Sum() in one and the Max() in the second. Check the objects IDs in the tables' properties dialog,

Then recalculate by making different selections in some field that would affect the calculation results. Then open Document Properties | Sheets and look up the calculation time (Calc Time in the lower box). The relative times will give an indication of which expression is faster.

It helps if your data set is quite large.

Community Browser