Aggr function with if Statement

[MarioC]

The objective here is that the column "Ventas" in Table 2 displays  the same amount as in Table 1.

"Ventas" is dependable from "Agente"(Employee) and Cliente (Client)

Table 2 does NOT has the column 'Client' so an aggr function is needed.

"Ventas"  in Table 1 is calculated by multiplying "Completado"  *  " Ventas Totales".

"Completado" is calculated with the following expression:

=if(
(sum(distinct B_Nombre)+
sum(distinct B_Direccion)+
sum(distinct B_email)+
sum(distinct B_Especie)+
sum(distinct B_RFC)+
sum(distinct B_Telefono) )= 6,
1,
0)

And Conteo is the sum of the last 6 columns, which are independent dimension (not calculated on set analysis)

I use distinct because the value might repeat, and i just need to know if the value is 0 or not.

But i dont get to figure out if its possible to use the if statment inside the aggr statement....

This is the expression i was trying to calculate for the column Ventas on Table 2 but its wrong...

aggr(Sum([Ventas Totales])*

if( (sum(distinct B_Nombre)+
      sum(distinct B_Direccion)+
      sum(distinct B_email)+
      sum(distinct B_Especie)+
      sum(distinct B_RFC)+
      sum(distinct B_Telefono) )= 6,1,0            ) ,Cliente,Agente)

Table #1

Table #2

[jcf]

I don't think you need that if statement at all.  try just aggr(sum(subt),agent).  having client in the group by is causing the aggr to aggregate to the client level.

[MarioC]

I need the if statement to convert to zero the sum of "Ventas Totales " where the sum of the last six columns is less than six.

So, if the sum of the last six columns is less than 6 than "Ventas" will equal to zero.

aggr(sum(subt),agent) will just be equal to "Ventas Totales"