topic problem with aggr function in QlikView

problem with aggr function

Wed, 13 Jul 2011 07:37:31 GMT

Hi guys, i've got e big problem....

I'm trying to apply aggr function on a big data set on qv project(over 13.000.000 records) but something'wrong and qv goes in crash, this is my function :

=NUM(count({$< date_in_e2e=, date_out_e2e=, date_ava=>} distinct if(date_in_e2e>=date_lo and date_in_e2e<=date_hi and flag_ultimo_aggiornamento=1 and AGGR(count(distinct codice), codice_cliente) >5 and AGGR(count(distinct codice), codice_cliente) <10, codice_cliente)), '#.##0')

Qv not responding and i've to close and restart the pc !!!

This is on a pivot chart with one dimension and one expression(this one)

Pls, may you help me ?

Thx a lot.

problem with aggr function

Wed, 13 Jul 2011 07:51:11 GMT

HI,

seems a complex formula. Best is to build it up piece by piece to be sure all underlying pieces gives the expected rsult.
For instance make a column with:

expr1: AGGR(count(distinct codice), codice_cliente)

Then

Expr2:

=NUM(count({$< date_in_e2e=, date_out_e2e=, date_ava=>} distinct if(date_in_e2e>=date_lo and date_in_e2e<=date_hi and flag_ultimo_aggiornamento=1 and column(1) >5 and column(1)), '#.##0')

You can make express1 invisible if you do not want to see it but to start you will see at least if the value calculated is correct. Alos it should improve performance as you aggr only once instead of twice.

Regards

problem with aggr function

Wed, 13 Jul 2011 08:44:28 GMT

I tried your suggestion, but something's wrong, the new expression is:

=NUM(count({$< date_in_e2e=, date_out_e2e=, date_ava=>} distinct

if(date_in_e2e>=date_lo and date_in_e2e<=date_hi and flag_ultimo_aggiornamento=1 and (column(1) >0 and column(1)<5), '#.##0')

That's on second expression, the first is : AGGR(count(distinct codice), codice_cliente)

but it give always null values.....

problem with aggr function

Wed, 13 Jul 2011 08:51:15 GMT

Hi,

Hard to help as such. Can you add the application with limited data and just the chart concerned?

Does it work without the set analyses?

Regards,

Jonathan

Re: problem with aggr function

Wed, 13 Jul 2011 10:35:18 GMT

Hi, i attacched a small version of the main project(in the first post), with few datas, as you can see in this case all works fine on the first sheet(i made it first i open post on forum)

I added a new sheet where there are your suggestions(i hope i have done correctly), pls take a look.

unfortunatly, If i load entire db data qv goes in crash caused by aggr function, is there another way to obtain the same result without aggr?

Thx a lot for your help.

problem with aggr function

Wed, 13 Jul 2011 11:14:46 GMT

Hi,

You need to remove the last 3 columns and tehn you are starting to have some results.
Solve formula by formula...

Regards,

Jonathan