count the highest number of management

[karelibarcar]

Hello everybody,

Hello everyone, I need your help, I have to count the last recorded management for each client, but I do not know how to do it, can you help me? The management that must be counted is the one with the highest Num_Gestion value, but I am using a variable because the client can select any range of dates to obtain the report. Please, your help, I've been trying to solve it for 3 days. Thank you very much

The formula found in Intersección is:

count

( Distinct

{

(

(<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=>} Num_Cedula_Cliente)>

*

<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>

)

)

}

Num_Cedula_Cliente

)

[karelibarcar]

Hi,

Now, I modified the expression, as you can see I no longer have repeated clients, but the condition that Num_Gestion is equal to the maximum is not fulfilled yet.

Sum(Aggr(

count(distinct

{(

<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>

*

<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>

*

<Num_Gestion=P({<Num_Gestion = {'=Aggr($(=Max(Num_Gestion)), Num_Cedula_Cliente)'}>}Num_Gestion)>

)}

Num_Cedula_Cliente)

, Proyecto, Descripcion_Gestion, Num_Cedula_Cliente))

Please, save me!

[karelibarcar · Accepted solution]

Thanks for your help.

I had to change the dimension by:

Aggr(FirstSortedValue(Descripcion_Gestion, -Num_Gestion), Num_Cedula_Cliente)

And the measure by:

Sum(Aggr(

count(distinct

{(

<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>

*

<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=>}Num_Cedula_Cliente)>

)}

Num_Cedula_Cliente)

*

Count(Aggr(FirstSortedValue(Descripcion_Gestion, -Num_Gestion), Num_Cedula_Cliente))

, Proyecto, Descripcion_Gestion, Num_Cedula_Cliente))

Now I have what I wanted.

[campbellr]

Hi Karen

I'm glad you got there in the end. I'm afraid my suggestion hasn't been needed in your end result but I'm glad you have a solution.

Ron