Help! use Aggregation function or use Set Analysis

[pjin_precima]

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

PJ

[swuehl]

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.

[pjin_precima]

Thanks swuehl,

I will test that.