Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions

Re: Latest\Earliest Value

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

11 Replies
Not applicable

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

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

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

Not applicable

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

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

Not applicable

Re: Latest\Earliest Value

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

Give this a try:


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

Not applicable

Re: Latest\Earliest Value

Thank you Sunny

Re: Latest\Earliest Value

Not a problem

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

Best,

Sunny