4 Replies Latest reply: Mar 19, 2017 10:13 AM by olga avalos RSS

    Max of Date by Specific Dimension

    olga avalos

      Hi,

       

      Please help me!!!!.

       

      I need to calculate the max value of date by dimension: Cod_Contratante. Example:

       

       

      Cod_Contratante Date            

      1                         1/2/2017

      1                         2/2/2017

      2                         4/2/2016

      2                         3/3/2017

       

      And I need this result:

       

      Cod_Contratante Date           Max(Date)           

      1                         1/2/2017     2/2/2017

      1                         2/2/2017     2/2/2017

      2                         4/2/2016     3/3/2017

      2                         3/3/2017     3/3/3017

       

      I want to calculate tha max of date by Cod_Contratante.

        • Re: Max of Date by Specific Dimension
          Enrique Colomer

          Try with this:

          =Date(Max(Date),'DD/MM/YYYY')

           

          Script:

           

          DATA:

          LOAD * Inline [

          Cod_Contratante, Date           

          1,1/2/2017

          1,2/2/2017

          2,4/2/2016

          2,3/3/2017

          ];

           

           

          MAX:

          LOAD Cod_Contratante, max(Date) as MaxDate resident DATA group by Cod_Contratante;

           

          Result:

          p_1233908.png

            • Re: Max of Date by Specific Dimension
              olga avalos

              thanks for your help, but If I select a date, MaxDate no change. And I need to change the date according to the selection of date.

               

              If I select date 4/2/2017, I hope this result:

               

               

              Cod_Contratante Date          Max(Date)        

              1                        1/2/2017    2/2/2017

              1                        2/2/2017    2/2/2017

              2                        4/2/2016    4/2/2017

              2                        3/3/2017    3/3/3017


              this  last registry did not exit in my current selection, and the Max(Date) change.


              Please What can I do?


            • Re: Max of Date by Specific Dimension
              Massimo Grossi

              max(total <Cod_Contratante> Date)      

                • Re: Max of Date by Specific Dimension
                  olga avalos

                  Hi,

                   

                  I have another problem:

                   

                     

                  Año_Emision_PolizaMes_Emision_PolizaNum_PolizaEstado_PolizaCod_ContratanteFecha_Emision_PolizaMax(Total<Cod_Contratante> Fecha_Emision_Poliza)
                  2016dic0000001302CADUCADA02179003766500109/12/201616/12/2016
                  2016dic0000002360ANULADA02179003766500109/12/201616/12/2016
                  2016dic0000002492VIGENTE02179003766500112/12/201616/12/2016
                  2016dic0000004626VIGENTE02179003766500112/12/201616/12/2016
                  2016dic0000002492VIGENTE02179003766500116/12/201616/12/2016
                  2016dic0000002501VIGENTE02179003766500116/12/201616/12/2016
                  2016dic0000001491RENOVADA00091013452704/12/201627/12/2016
                  2016dic0000001454VIGENTE00091013452721/12/201627/12/2016
                  2016dic0000003134CANCELADA00091013452727/12/201627/12/2016

                   

                   

                  Now, I can count Cod_Contrante, but only Estado_Poliza-={'CANCELADA','ANULADA'} and with the max(Fecha_Emision).

                   

                  Count(distinct {<Fecha_Emision_Poliza={'=Max(Total<Cod_Contratante> Fecha_Emision_Poliza)'},Estado_Poliza-={'CANCELADA','ANULADA'}>}Cod_Contratante)

                   

                  and I hope my result : Count(distinct {<Fecha_Emision_Poliza={'=Max(Total<Cod_Contratante> Fecha_Emision_Poliza)'},Estado_Poliza-={'CANCELADA','ANULADA'}>}Cod_Contratante)=1 but it is not my result.