Max date

[dmxmikey]

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

[YoussefBelloum]

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 ?

[dmxmikey]

where Act.date is <= (today)

[vishsaggi]

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))

[shiveshsingh]

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

W:

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;

[zebhashmi]

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

[Digvijay_Singh]

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;