topic Re: Latest\Earliest Value in QlikView

Latest\Earliest Value

Sun, 20 Sep 2015 09:22:34 GMT

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

Sun, 20 Sep 2015 10:22:57 GMT

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 — Sun, 20 Sep 2015 13:28:47 GMT

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 — Sun, 20 Sep 2015 14:26:18 GMT

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

Re: Latest\Earliest Value

Mon, 21 Sep 2015 06:54:56 GMT

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 — Mon, 21 Sep 2015 07:12:31 GMT

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

Re: Latest\Earliest Value

Mon, 21 Sep 2015 07:28:53 GMT

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

Re: Latest\Earliest Value

sunny_talwar — Mon, 21 Sep 2015 07:40:53 GMT

Give this a try:

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

Re: Latest\Earliest Value

Mon, 21 Sep 2015 07:44:31 GMT

Thank you Sunny

Re: Latest\Earliest Value

sunny_talwar — Mon, 21 Sep 2015 07:51:35 GMT

Not a problem

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

Best,

Sunny

Re: Latest\Earliest Value

sasiparupudi1 — Mon, 21 Sep 2015 07:59:43 GMT

try

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

Re: Latest\Earliest Value

Mon, 21 Sep 2015 08:10:45 GMT

Thank you Sasidhar Parupu