topic Re: Max date in QlikView

Max date

dmxmikey — Wed, 25 Nov 2020 16:16:04 GMT

I have the following data and need to filter the data to show only the line which the price is valid as today. so need to show only WSP1 as 9.11 and date 02/02/2018

Act. Date	WSP 1
01/01/2013	8.850
01/02/2013	8.850
06/01/2014	9.070
17/03/2014	5.990
23/03/2016	8.850
05/01/2017	9.110
02/02/2018	9.110
06/04/2018	11.870

Re: Max date

YoussefBelloum — Wed, 14 Feb 2018 16:52:50 GMT

Hi,

can you be more price about what you want to do ?

how do you know what is the valid price for today ? otherwise you will always filter on one static value

try to attach some data, or a QVW maybe ?

Re: Max date

dmxmikey — Wed, 14 Feb 2018 16:56:54 GMT

where Act.date is <= (today)

Re: Max date

vishsaggi — Wed, 14 Feb 2018 17:02:25 GMT

May be try Firstsortedvalue() function in your chart or text box.

= Firstsortedvalue(WSP1, -Aggr(Act.Date, ActDate, WSP1, ActDate))

= Firstsortedvalue(Act.Date, -Aggr(Act.Date, ActDate, WSP1, ActDate))

Re: Max date

shiveshsingh — Wed, 14 Feb 2018 17:56:22 GMT

May be this

T:load * Inline [

ActDate, WSP

01/01/2013, 8.850

01/02/2013 ,8.850

06/01/2014 ,9.070

17/03/2014, 5.990

23/03/2016, 8.850

05/01/2017, 9.110

02/02/2018, 9.110

06/04/2018, 11.870];

NoConcatenate

load date(date#(ActDate,'DD/MM/YYYY'),'DD/MM/YYYY') as ActDate,WSP

Resident T

where date(date#(ActDate,'DD/MM/YYYY'),'DD/MM/YYYY')<= date(today(),'DD/MM/YYYY');

drop table T;

NoConcatenate

S:load max(date(date#(ActDate,'DD/MM/YYYY'),'DD/MM/YYYY')) as MaxActDate

Resident W;

let vMAX = Peek('MaxActDate');

NoConcatenate

Final:load ActDate, WSP

Resident W

where ActDate = $(vMAX);

drop table W;

Re: Max date

zebhashmi — Wed, 14 Feb 2018 18:50:27 GMT

=only({<ActDate={'<Today()'}>}WSP1)

Re: Max date

Digvijay_Singh — Wed, 14 Feb 2018 19:03:53 GMT

May be this -

Table1:

Load Date(Date#(ActDate,'DD/MM/YYYY')) as ActDate,WSP1 inline [

ActDate WSP1

01/01/2013 8.850

01/02/2013 8.850

06/01/2014 9.070

17/03/2014 5.990

23/03/2016 8.850

05/01/2017 9.110

02/02/2018 9.110

06/04/2018 11.870 ](delimiter is spaces);

NoConcatenate

Table2:

Load *,Date(Peek(ActDate)-1) as EndDate

Resident Table1

Order by ActDate Desc;

Drop table Table1;