Last date with value

[jsobrinho]

Hi team,

I would like show the last date with value.

for example:

If I select 22/06/2018 I Would like show the values from 17/06/2018 because this is my last date with value

=Sum({<Date={"$(=date(max(Date)-1,'YYYY-MM-DD'))"}>} Valor)

If I select 22/06/2018 and 17/06/2018 I Would like show the values from 14/06/2018 and 13/06/2018 because I selected two dates.

=Sum({<Date={"$(=date(max(Date)-2,'YYYY-MM-DD'))"}>} Valor)

[sunny_talwar]

Which object you want to show this in?

[jsobrinho]

I need to show the last date with value, by selection

[sunny_talwar]

In a text box object? in a chart?

[jsobrinho]

text box

[sunny_talwar]

Try this

=Sum({<Date = {"$(='>=' & Date(Max({<Date = {[$(='<' & Min(Date))]}>}Date, GetSelectedCount(Date))) & '<=' & Date(Max({<Date = {[$(='<' & Min(Date))]}>}Date)))"}>}Valor)

[jsobrinho]

Thank you so much for the reply, but the problem is, When I have one date without data, in your example:

Table:

LOAD * INLINE [

    Date, Valor

    06/13/2018, 1

    06/14/2018, 2

    06/15/2018,

    06/17/2018, 30

    06/22/2018, 40

];

If I selected 22 and 17 days, I would like show data from 13 and 14 ( sum(Valor) = 3 )because I don't have value in 15 jun

Do I make myself clear?

[sunny_talwar]

Try this

=Sum({<Date = {"$(='>=' & Date(Max({<Date = {[$(='<' & Min(Date))]}, Valor = {

}

>}Date, GetSelectedCount(Date))) & '<=' & Date(Max({<Date = {[$(='<' & Min(Date))]}, Valor = {

}>}

Date)))"}>}Valor)

[jsobrinho · Accepted solution]

Thank you for reply,

I solved:

I created a second table com ID colum with Valor >0

TEMP:

LOAD DISTINCT DATA

     FROM

(ooxml, embedded labels, table is Planilha1)

Where VALOR > 0;

DATA_ORDER:

LOAD

DATA,

DATA AS DATA_V,

RecNo() as ID_DATA

Resident TEMP Order By DATA;

My expression:

=SUM({<ID_DATA={">=$(vTeste)  < $(=MIN(ID_DATA))"}, DATA=>} VALOR)

where vTeste is Min ID_DATA