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

    count the highest number of management

    Karen Barberán

      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

      )

       

        • Re: count the highest number of management
          Ron Campbell

          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
              Karen Barberán

              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?

            • Re: count the highest number of management
              Bala Bhaskar
              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))
                • Re: count the highest number of management
                  Karen Barberán

                  Hi,

                   

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

                   

                  auxilioo.png

                    • Re: count the highest number of management
                      Karen Barberán

                      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!

                       

                      auxilioo.png

                        • Re: count the highest number of management
                          Karen Barberán

                          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.