Aggr function with if Statement

MarioC — Sat, 16 Nov 2024 02:35:44 GMT

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

Re: Aggr function with if Statement

jcf — Fri, 24 Apr 2020 19:34:09 GMT

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.

Re: Aggr function with if Statement

MarioC — Fri, 24 Apr 2020 21:51:30 GMT

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"

topic Re: Aggr function with if Statement in App Development

Aggr function with if Statement

Re: Aggr function with if Statement

Re: Aggr function with if Statement