11 Replies Latest reply: Sep 21, 2015 4:10 AM by Nir Gil RSS

    Latest\Earliest Value

      Hello,

      I am new in Qlik.

      I have asked this question in the past but now I created an example for my data ( I am working with SQL DB but I hope this example will do).

      I wish to show, calculate, find the earliest and the latest value (by date) from my Data Base. (See text Box in red)

       

      Not the Max or Min value!

       

      I have searched for the formula in the forum but with no success.

       

      Can any one help?

       

      Thank you

       

      Nir

        • Re: Latest\Earliest Value

          BTW - in my real DB the Date field is Day\Month\Year field.

          And the SUM expression is an aggregation.

            • Re: Latest\Earliest Value
              Sunny Talwar

              May be look at this for an example: Value Associated with Min/Max Value of Another Field (Front End Solution)

               

              I hope this will help.

               

              Best,

              Sunny

              • Re: Latest\Earliest Value
                Sunny Talwar

                For you example, are you looking for this?

                 

                Capture.PNG

                 

                Expression for Max Date Value:

                 

                • For Quantity 1: ='Qunatity1 on ' & FirstSortedValue(Date, -Date) & ': ' & FirstSortedValue(Qty1, -Date)
                • For Quantity 2: ='Qunatity2 on ' & FirstSortedValue(Date, -Date) & ': ' & FirstSortedValue(Qty2, -Date)

                 

                Expression for Min Date Value:

                 

                • For Quantity 1: ='Qunatity1 on ' & FirstSortedValue(Date, Date) & ': ' & FirstSortedValue(Qty1, Date)
                • For Quantity 2: ='Qunatity2 on ' & FirstSortedValue(Date, Date) & ': ' & FirstSortedValue(Qty2, Date)

                 

                If this is not what you are looking for, can you let us know what you want. Unfortunately, I am working with personal edition of QlikView and can't open the qvw you have shared.

                 

                HTH

                 

                Best,

                Sunny

                  • Re: Latest\Earliest Value

                    Hi Sunny,

                    Thank you for your reply but this is not what I am looking for.

                    Q1 and Q2 are only an example to the fact that in my real DB i have aggregated qty.

                    I am looking for an expression which display the latest date value from the selected time period.

                    It should be simple.

                    If I pick the Jan and Feb I would like to see the qty value of the last day in Feb.

                    If I pick the year 2014 I would like to see the qty value of the last day of the year.

                    If I pick from  16.11.14 to 13.06.15 I would like to see the qty value of the date 13.06.15

                    If I pick 4 dates  the first of month of the first quarter of 2015 (1.1.15,  1.2.15,  1.3.15,  1.4.15) I would like to see the qty value of the 1.4.15.

                    In my data there is a qty value for each day (input) and my qty value is an aggregated expression since the user can select the qty value of  s single container, or few containers from one city or few containers from few cities from a certain country and Set.  The aggregated expression exist and works fine.

                    In addition , when I pick a time period (using my day and month and year list boxes) I see the relevant qty on a line chart. I just need an extra text box to show me the first and last input qty value from the time period I selected (The final goal is to use the first qty value minus the last qty value and to calculate how much was "lost"

                     

                    I hope I am clear now

                     

                    Nir

                • Re: Latest\Earliest Value
                  kushal chawda

                  Your expression is correct in text box

                   

                  Sum({<Date={'$(=Max(Date))'}>} Qty1)

                   

                  You just need to convert your Date from Text to Proper date format, so that your expression will work.

                   

                  Convert your date as below

                   

                  date(date#(Date,'DD.MM.YY'),'DD/MM/YYYY') as Date