Set analysis nested

[agigliotti]

Hello,

I have the following expression in a straight table:

count( {< _FATTURATOFlag = {"S"}, [Anno fattura] = {"$(vAPP)"}, [MMGG fattura] = {">=$(vMMGGdal)<=$(vMMGGal)"},

[Codice cliente] = {'=sum( {< _FATTURATOFlag = {"S"}, [Anno fattura] = {"$(vAPP)"}, [MMGG fattura] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} [Importo netto riga] )>0'}

>} DISTINCT [Codice cliente])

with a dimension named "Wine type".

I need to count the customers ([Codice cliente]) with Sum([Importo netto riga]) > 0 for each dimension value (Wine type).

I noted it's working fine only if I select a single dimension value, else I get a number greater than the correct one.

Can somenone help me to achieve it?

Many thanks in advance.

Best Regards

Andrea

[whiteline]

Hi.

Could you provide vAPP and vMMGGdal, vMMGGal definition. And describe the relation between Codice cliente and Wine type.

The entire sample would be great.

[agigliotti]

ex. vAPP = 2012 (year) ; vMMGGdal = 0101 (as MMDD related to sales date) ; vMMGGal = 1231 (as MMDD related to sales date)

currently we are exploring wine sales for type and [Codice Cliente] is the customer code.

[#4]

If you post a sample application, it will be easier to troubleshoot. 

Anyway, have you tried to aggregate your count by Wine Type and Client?

aggr(

count( {< _FATTURATOFlag = {"S"}, [Anno fattura] = {"$(vAPP)"}, [MMGG fattura] = {">=$(vMMGGdal)<=$(vMMGGal)"},

[Codice cliente] = {'=sum( {< _FATTURATOFlag = {"S"}, [Anno fattura] = {"$(vAPP)"}, [MMGG fattura] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} [Importo netto riga] )>0'}

>} DISTINCT [Codice cliente]), [Wine Type], [Codice cliente])

[stevedark]

You are likely to be needing an Aggr statement in there somewhere, to do one aggregation inside another.

Taking the Set Analysis out of the equation, to count Customers with sales over 100K you would have:

=sum(aggr(if(sum(Amount) > 100000, 1, 0), Customer)

You will find plenty written about Aggr elsewhere, but the basic premise of what is going on here is that for every Customer (the second parameter of the Aggr statement) you are doing the nested expression.  Each customer will then have a value of either 0 or 1 assigned to it.  The sum on the outside then tots up those flags.

If you want to apply Set Analysis to the expression then you can place separate Set Analysis expressions just inside each of the sum functions.  If your set analysis is limiting which customers are involved in the expression you will need to repeat the same set analysis.  If all customers are included then you probably only need the set analysis in the inner sum.

Another use case for Aggr is if you want to show in a text box an average value by a Field (or group of fields) in a text box (ie. without listing all values).  The expression would look like:

=avg(aggr(sum(Amount), Customer)

The important thing is that any time you might consider nesting more than one aggregation then the Aggr statement will be your friend.

Hope that helps.

Steve

Quick Intelligence

[agigliotti]

I tried the expression below:

=sum( {< _FATTURATOFlag = {"S"}, [Anno fattura] = {"$(vAPP)"}, [MMGG fattura] = {">=$(vMMGGdal)<=$(vMMGGal)"} >}

aggr(if(sum( {< _FATTURATOFlag = {"S"}, [Anno fattura] = {"$(vAPP)"}, [MMGG fattura] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} [Importo netto riga] )>0, 1, 0), [Codice cliente]) )

but I have the same wrong result.

[agigliotti]

Finally, using the below expression:

sum( aggr( if(sum( {< _FATTURATOFlag = {"S"}, [Anno fattura] = {"$(vAPP)"}, [MMGG fattura] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} [Importo netto riga] )>0, 1, 0),

[Wine type], [Codice cliente]) )

Now I get the correct value for each wine type but the expression total is wrong because it sums all rows value rather than the COUNT DISTINCT [Codice cliente].

[stevedark]

Hi Andrea,

Glad we are getting closer now!  Do you get the right result if you change the total mode from Expression Total to Sum Of Rows?

What dimension do you have on the chart, is that wine type?

Do you wish to count each client only once in the total - even if that client is counted on multiple rows?

If I have understood the requirement, it may be that you need to drop the Wine Type out of the Aggr parameters?

- Steve

[agigliotti]

Hi Steve,

No I get the same wrong result using either "Expression Total" or "Sum of Rows" option.

In both cases I get the sum of all rows values.

Yes I have the "win type" as dimension.

Yes I have to count customers only once with sales amount > 0 even if that customer is counted on multiple rows.

I'll let you know.

Thanks

Andrea

[stevedark · Accepted solution]

You could try returning the client code in the aggregation, so that you can count it at both the dimensional and the total level:

count(DISTINCT aggr( if(sum( {< _FATTURATOFlag = {"S"}, [Anno fattura] = {"$(vAPP)"}, [MMGG fattura] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} [Importo netto riga] )>0, [Codice cliente], null()),

[Wine type], [Codice cliente]) )

Steve