Latest\Earliest Value

[#1]

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

[#2]

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

And the SUM expression is an aggregation.

[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

[sunny_talwar]

For you example, are you looking for this?

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

[#5]

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

[Kushal_Chawda · Accepted solution]

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

[#7]

Thank you very much Kush - It worked.

One more silly problem, in my text box , when I write:

=Sum({<Date={'$(=MIn(Date))'}>} Qty1+Qty2)  -  Sum({<Date={'$(=Max(Date))'}>} Qty1+Qty2)    - - - - -   It Good.

But when I add a syntax before:

= 'Lost qty = ' & Sum({<Date={'$(=MIn(Date))'}>} Qty1+Qty2)  -  Sum({<Date={'$(=Max(Date))'}>} Qty1+Qty2)

It fail , result a dash ( - ) sign.

BTW , when I write only :

='Latest Value = ' & Sum({<Date={'$(=Max(Date))'}>} Qty1+Qty2)    It is OK.

Nir

[sunny_talwar]

Give this a try:

= 'Lost qty = ' & (Sum({<Date={'$(=MIn(Date))'}>} Qty1+Qty2)  -  Sum({<Date={'$(=Max(Date))'}>} Qty1+Qty2))

[#9]

Thank you Sunny

[sunny_talwar]

Not a problem

If you got what you want, I suggest closing this thread by marking the correct answer

Best,

Sunny