Help! use Aggregation function or use Set Analysis

pjin_precima — Fri, 03 Feb 2012 21:15:37 GMT

Hi,

I need to create a weekly trend chart to analyze the % of trips by week where 1/2/3/4/5/6+ units got purchased. But because our data volume is large, the performance is very bad.

Any recomendations?

(denominator is the total trips where units purchased > 0, nominator is the number of trips where units purchased =1

here is my expression using aggr functions for % of trips where units purchased = 1

count(distinct if( aggr(Sum ({<yr_wk = {'>=$(=vMinWK)<=$(=vMaxWK)'}>} quantity), trip_id)=1, trip_id))

count(distinct if(aggr(Sum ({<yr_wk = {'>=$(=vMinWK)<=$(=vMaxWK)'}>} quantity), trip_id)>0, trip_id))

here is my expression using set analysis for % of trips where units purchased = 1

count({<trip_id= {"=Sum({<yr_wk = {'>=$(=vMinWK)<=$(=vMaxWK)'}>} quantity)=1"}>} distinct trip_id)

count({<trip_id= {"=Sum({<yr_wk = {'>=$(=vMinWK)<=$(=vMaxWK)'}>} quantity)>0"}>} distinct trip_id)

Both expressions give the same results, but seems aggr function performs faster.

Much appreciated if anyone could help with this. if you have any recommandation to improve the performance of the expression, please let me know.

Thanks

Help! use Aggregation function or use Set Analysis

swuehl — Fri, 03 Feb 2012 22:00:12 GMT

You could consider replacing the count(distinct trip_id) with a sum. Please refer to the manual, if I remember correctly chapter C.2 of book III, "Application Performance Optimization - Count (Distinct 'FieldName')", for some methods to do this.

Help! use Aggregation function or use Set Analysis

pjin_precima — Fri, 03 Feb 2012 22:07:26 GMT

Thanks swuehl,

I will test that.

topic Help! use Aggregation function or use Set Analysis in QlikView

Help! use Aggregation function or use Set Analysis

Help! use Aggregation function or use Set Analysis

Help! use Aggregation function or use Set Analysis