topic Re: What is Faster, a Sum or a Max in QlikView

What is Faster, a Sum or a Max

Anonymous — Tue, 07 Nov 2017 09:22:46 GMT

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:

Sum(IsConnected)
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:

Sum(Daily_Conected_Hours)
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:

Day	IsConected	Daily_Conected_Hours	Accum_Conected_Hours
1	1	10	10
2	1	5	15
3	1	8	23
4	0	0	23
5	1	4	27
6	0	0	27
7	1	5	32
8	1	8	40
9	1	10	50
10	1	5	55
11	0	0	55

Re: What is Faster, a Sum or a Max

jonathandienst — Tue, 07 Nov 2017 09:33:40 GMT

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.

Re: What is Faster, a Sum or a Max

Anonymous — Wed, 08 Nov 2017 08:14:01 GMT

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

Re: What is Faster, a Sum or a Max

jonathandienst — Wed, 08 Nov 2017 12:13:18 GMT

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.