2 Replies Latest reply: Nov 12, 2012 8:44 PM by Guzman Garcia RSS

    Aggr expression to solve dynamic Date selection

    Guzman Garcia

      Hi,

      I need help with the following problem.

       

      I have a table with the following format:

      Image.png

       

      What we need is the following:

      - Get the max DATE for each Field2,       -- Aggr (Max (Date), Field2).

      - The result of the above, do a count distributed by Field3.

       

      For Example:

       

      Field3       Count

      Category1     3

      Category2     1

      Category3     2

       

       

      Dimension: Field3

      Expression: Aggr(Max(Fecha), Field2)  -- and a count by Field3.

       

      NOTE: I need resolve this in object level because the field DATE is select  dynamic by the user.

       

      I attach a QlikView document.

       

       

      Can anyone help?

      Thank you very much!

      Guzmán

        • Re: Aggr expression to solve dynamic Date selection
          Gysbert Wassenaar

          It's not clear to me what you want exactly. You can't get the max dates and the counts per category in one table. You'll need two tables.

           

          I'm guessing that a user selects one or more dates and wants to see the max dates of the selected dates per Field2 and the count per category where the date is smaller or equal to the max date of the selected dates. See the attached qvw file.

           

          If that's not what you need you'll have to explain what you need. You could use excel to make example tables the way you want the result to look.

            • Re: Aggr expression to solve dynamic Date selection
              Guzman Garcia

              Gysbert thank you very much for the quick response.

               

              I adjusted based on your qvw, and then explain a particular case.

               

              Image.png

               

              In the previous image, select the first 4 values from Table1. In Table2 I have records filtered by that selection.

              As can you see, each Field2 value may correspond to several Date values and Field3 values.

              I need only count Field2 values for which Date value is maximum.

               

              In the example:

              Field2     Date

              123456 - 06/10/2012

              456123 - 09/10/2012

               

              Then link these values with the corresponding Field3:

               

              Date             Field2      Field3

              06/10/2012 - 123456 - Category3

              09/10/2012 - 456123 - Category1

               

              And finally get a count by Field3:

               

              Field3       Count

              Category1 - 1

              Category3 - 1

               

              Is a count of Field2 distributed by Field3, but taking only the max Date value for each Field2 value and its corresponding Field3 value.

               

              It's a little hard to explain, I hope I have helped. I attached the qvw file and a Excel with some examples.

               

              Thank you very much

              Guzmán