Aggr Function

[#1]

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])

[tresesco]

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.

[simondachstr]

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).

[christian77]

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.

[christian77]

Hi again.

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

[#6]

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 ?