topic Re: Optimizing a Calculation [sum(aggr(if(...)))] in QlikView

Optimizing a Calculation [sum(aggr(if(...)))]

Anonymous — Fri, 06 Jun 2014 11:27:41 GMT

Good morning,

I need to calculate the disting companies that meet some requerements (KEY_EMPRESA). This formula works perfectly, with the drawback that it takes more that 3 minutes to run, wich is too much time. We have upgraded the server (more RAM, CPU, ..), but the time did not changed.

[WORKS] - sum(aggr(if(Orden_ACT=max(total<KEY_EMPRESA> Orden_ACT) ,1,0),KEY_EMPRESA, Orden_ACT))

I am trying to rewrite this formula in order to make it more efficient... with something like: [where CONTADOR is allways 1)

[EXAMPLE - DOES NOT WORK] - aggr(sum({<Orden_ACT={max(total<KEY_EMPRESA> Orden_ACT)} >} CONTADOR), KEY_EMPRESA, Orden_ACT)

I am blocked at this point. Do you have any idea??

Thanks in advance,

Javier

Re: Optimizing a Calculation [sum(aggr(if(...)))]

tresB — Fri, 06 Jun 2014 11:29:44 GMT

What are your dimensions in the chart? Or, is it somewhere in textbox?

Re: Optimizing a Calculation [sum(aggr(if(...)))]

Anonymous — Fri, 06 Jun 2014 11:32:53 GMT

We have two dimension, to build the following table data:

ACTIVITY DESCRIPTION	TYPE	H	M	E	Total
AGENTES INMOBILIARIOS [2197]		-	-	4	4
AGENTES Y REPRESENTANTES COMERCIALES [3478]		5	-	7	12
AGRICULTURA [2151]		17	1	276	294
AGRICULTURA CONSULTORIA [4952]		-	-	1	1
Total		22	1	288	311

Regards,

Javier

Re: Optimizing a Calculation [sum(aggr(if(...)))]

tresB — Fri, 06 Jun 2014 11:40:37 GMT

Is it :

KEY_EMPRESA - Activity Description

Orden_ACT - Type ?

If so, you can possibly avoid using Aggr() and that would significantly improve performance.

Re: Optimizing a Calculation [sum(aggr(if(...)))]

Anonymous — Fri, 06 Jun 2014 11:44:38 GMT

Unfortunately NOT.

KEY_EMPRESA = is the company code. Is it used to count the distinct numer of companies that meet the requirements

ORDEN_ACT = is a number that it is used to decide where to count the company. The company must be counted where the ORDEN_ACT is the maximum for the given KEY_EMPRESA.

ACTIVITY_DESCRIPTION = is an dimension that must be in ROWS.

TYPE = Another dimension that must be in COLUMNS

Hope it is more clear now,

Javi

Re: Optimizing a Calculation [sum(aggr(if(...)))]

tresB — Fri, 06 Jun 2014 12:02:21 GMT

Try removing 'total<KEY_EMPRESA>' part from the expression.

Re: Re: Optimizing a Calculation [sum(aggr(if(...)))]

Anonymous — Fri, 06 Jun 2014 12:14:16 GMT

I did try, but I am not getting the same values (which we verified are ok).

Find a sample QV attached.

Best

Javier