topic Value for the max date in QlikView

Value for the max date

Fri, 21 Mar 2014 08:01:42 GMT

Hi all, I have the following data set:

Name Date Value

Vika 20.09.2010 1

Vika 13.01.2009 0,5

Dan 01.01.2008 2

Dan 02.04.2009 1

Dan 06.02.2001 0,5

Kat 12.09.2011 2

Kat 11.08.2011 1

The results I want is:

Name Value

Vika 1

Dan 1

Kat 2

In other words I want to get Value for the max date.

Help me, please.

Re: Value for the max date

alexandros17 — Fri, 21 Mar 2014 08:12:07 GMT

Here is an example

Re: Value for the max date

MK_QSL — Fri, 21 Mar 2014 08:18:06 GMT

=FirstSortedValue(Value,-Date)

Re: Value for the max date

preminqlik — Fri, 21 Mar 2014 08:48:59 GMT

This can be done in two ways in script level and in frontend

//**********************IN SCRIPT LEVEL

Load

Name,

firstsortedvalue(Date,-Date) as Date,

firstsortedvalue(Value,-Date) as Value

group by Name;

Load *

from

XXX;

>>> In the above method use expression as sum(Value)

//********************************IN FRONT END

sum(aggr(firstsortedvalue(Value,-Date),Name))

Re: Value for the max date

Fri, 21 Mar 2014 09:41:48 GMT

You can Try This:

Take a staraight Table and add Name, Date as Dimensions.

Add an expression = Rank(Date), and Label it as RankDate Hide this expression by checking the "hide column" from presentation tab.

Add another expression was If(RankDate=1,Value,0).

Go to presentation tab and check the checkbox "Supress zero Values".

Hope this helps. It works in my case.

Re: Value for the max date

qv_jagan — Fri, 21 Mar 2014 09:49:39 GMT

Use this expression in the chart.

"=FirstSortedValue(Value,Date#(Date,'DD.MM.YYYY'),2)"

This will work.