Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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

auxilioo.png
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

)

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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.

View solution in original post

12 Replies
Highlighted
Creator
Creator

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

Highlighted
Contributor III
Contributor III

Thanks for your answer.


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=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=, Num_Gestion={$(vGestion)}>}           Num_Cedula_Cliente)>

         *

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

    )}

    Num_Cedula_Cliente

    ), Proyecto, Descripcion_Gestion))


  • Sum({<Num_Gestion={$(vGestion)}>}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

    ), Proyecto, Descripcion_Gestion))



What am I doing wrong?

Highlighted
Creator
Creator

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

Highlighted
Contributor III
Contributor III

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.

Highlighted
Creator
Creator

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

Highlighted

May be, modify your expression like this:

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

Highlighted

Sum(Aggr(count ( distinct
     {
     (
     (<Num_Cedula_Cliente=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=, Num_Gestion={'$(=vGestion)'}>}       Num_Cedula_Cliente)>
     *
     <Num_Cedula_Cliente=P({<Identificador_Campaña={'ID2'}, Descripcion_Gestion=, Num_Gestion={'$(=vGestion)'}>}Num_Cedula_Cliente)>)
)}
Num_Cedula_Cliente
), Proyecto, Descripcion_Gestion))
Highlighted
Contributor III
Contributor III

Hi,

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

auxilioo.png

Highlighted
Contributor III
Contributor III

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=P({<Identificador_Campaña={'ID1'}, Descripcion_Gestion=, total Num_Gestion>}Num_Cedula_Cliente)>

*

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

)}

Num_Cedula_Cliente

), Proyecto, Descripcion_Gestion))

I've changed vGestion by Max(total Num_Gestion)

Please, correct me!