12 Replies Latest reply: Aug 30, 2018 9:13 PM by Ron Campbell

# count the highest number of management

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

)

• ###### Re: count the highest number of management

Hi Karen

I'm thinking you want to add to your set analysis something about Num_Gestion = Max(Num_Gestion ) you'll need to dig a little to get the syntax right.

as a side note it might be worth adding to your post to explain the things you have tried to get a solution.

Ron

• ###### Re: count the highest number of management

I've been trying the following, but none has worked:

• Count(Aggr(Count({<Num_Gestion = {\$(vGestion)}>}Num_Gestion),Num_Cedula_Cliente))

• Sum(Aggr(count ( distinct

{

(

*

)}

Num_Cedula_Cliente

), Proyecto, Descripcion_Gestion))

• Sum({<Num_Gestion={\$(vGestion)}>}Aggr(count ( distinct

{

(

*

)}

Num_Cedula_Cliente

), Proyecto, Descripcion_Gestion))

What am I doing wrong?

• ###### Re: count the highest number of management

Hi Karen

What is the definition of vGestion?

For troubleshooting try to calculate the highest Num_Gestion per Proyecto, Descripcion_Gestion

Also you may need to include the field Proyecto in your table. Aggr needs the field present to be able to group by it, I think. Give it a try.

I'll dummy something up and see if I can get it to work.

Ron

• ###### Re: count the highest number of management

Hi Ron,

Sorry, i forgot to write the definition that is Max(Num_Gestion), without = because it doesn't work with =.

Thanks for your suggestion. I will add the field Proyecto, i hope it works.

Please, tell me if you find a good solution.

• ###### Re: count the highest number of management

Hi Karen

I've made something that works in my system for calculating the sum of a field given another field is the max value:

Sum({<[HS Price]={">=\$(=Max(total<[HS Supplier Name] >[HS Price]))"},

[HS Supplier Part Number Original]=P([HS Supplier Part Number Original])>}total<[HS Supplier Name]> PO_OrderCount)

I think the bit you have missed is use of TOTAL<> where you put the per field names within the <>'s

• ###### Re: count the highest number of management

Hi Ron,

So sorry, I think I didn't understand you. Your suggest is this:

Sum(Aggr(count ( distinct {<Num_Gestion={\$(vGestion)}>}

{

(

*

)}

Num_Cedula_Cliente

), Proyecto, Descripcion_Gestion))

I've changed vGestion by Max(total Num_Gestion)

• ###### Re: count the highest number of management

May be, modify your expression like this:

=Count(Aggr(Count({<Num_Gestion = {'\$(=vGestion)'}>}Num_Gestion),Num_Cedula_Cliente))

• ###### Re: count the highest number of management
 Sum(Aggr(count ( distinct { ( (} Num_Cedula_Cliente)> * }Num_Cedula_Cliente)>) )} Num_Cedula_Cliente ), Proyecto, Descripcion_Gestion))
• ###### Re: count the highest number of management

Hi,

Thanks for your answer. I tried it, but it still doesn't work.

• ###### Re: count the highest number of management

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_Gestion=P({<Num_Gestion = {'=Aggr(\$(=Max(Num_Gestion)), Num_Cedula_Cliente)'}>}Num_Gestion)>

)}

Num_Cedula_Cliente)

, Proyecto, Descripcion_Gestion, Num_Cedula_Cliente))

• ###### Re: count the highest number of management

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)

*

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

, Proyecto, Descripcion_Gestion, Num_Cedula_Cliente))

Now I have what I wanted.

• ###### Re: count the highest number of management

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