topic Re: Last date with value in Brasil

Last date with value

jsobrinho — Mon, 25 Jun 2018 18:57:51 GMT

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)

Re: Last date with value

sunny_talwar — Mon, 25 Jun 2018 19:03:27 GMT

Which object you want to show this in?

Re: Last date with value

jsobrinho — Tue, 17 Jul 2018 12:41:26 GMT

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

Re: Last date with value

sunny_talwar — Tue, 17 Jul 2018 12:42:14 GMT

In a text box object? in a chart?

Re: Last date with value

jsobrinho — Tue, 17 Jul 2018 13:20:52 GMT

text box

Re: Last date with value

sunny_talwar — Tue, 17 Jul 2018 13:56:39 GMT

Try this

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

Re: Last date with value

jsobrinho — Tue, 17 Jul 2018 18:05:12 GMT

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?

Re: Last date with value

sunny_talwar — Tue, 17 Jul 2018 18:15:57 GMT

Try this

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

}

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

Re: Last date with value

jsobrinho — Tue, 17 Jul 2018 19:13:59 GMT

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