topic Re: Aggr Function in QlikView

Aggr Function

Fri, 27 Sep 2013 09:02:02 GMT

I want to calculate average of TB if a set of conditions meet and then want to sort the data on the Customer ID.

Will the following Expression work ??

Aggr(avg(if([Customer Type]='P' and [Product Brand]='Xi'and [Target Status-X]='Y'and call>0,TB,0)),[Customer ID])

Re: Aggr Function

tresB — Fri, 27 Sep 2013 09:10:46 GMT

In first look, seems okay. You have a doubt and so have been here or might be not working as you expect in your case. Request to share sample app.

Re: Aggr Function

simondachstr — Fri, 27 Sep 2013 09:16:19 GMT

I am a little concerned about the else statement in your if. You will take an average among TB AND the many 0's which do not satisfy your if statement. Would that be technically correct?

If not, I suggest using the following way:

First of all try using set analysis and second use an expression similar to sum({set analysis} TB)/count({set analysis} TB).

Re: Aggr Function

christian77 — Fri, 27 Sep 2013 09:23:57 GMT

Hi:

I recomend you to use set analysis instead of ifs.

aggr(NODISTINCT avg({$<[Customer Type]={'P'}, [Product Brand]={'Xi'}, [Target Status-X]={'Y'}, call={'>0'}>} TB),[Customer ID])

Notice the NODISTINCT. Try it with and wihtout it depending where do you want your expression to be placed.

Re: Aggr Function

christian77 — Fri, 27 Sep 2013 09:25:43 GMT

Hi again.

If your dimension is the one in the aggregation you may not need the aggregation.

Re: Aggr Function

Fri, 27 Sep 2013 09:43:39 GMT

Hey Martin,

The Condition 'Call' in the if statement is a calculated expression it self, where Call= sum(call).

Can i add a Calculated Expression in set analysis.

If not,

is there any other way to do so ?