Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
Which object you want to show this in?
I need to show the last date with value, by selection
In a text box object? in a chart?
text box
Try this
=Sum({<Date = {"$(='>=' & Date(Max({<Date = {[$(='<' & Min(Date))]}>}Date, GetSelectedCount(Date))) & '<=' & Date(Max({<Date = {[$(='<' & Min(Date))]}>}Date)))"}>}Valor)
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?
Try this
=Sum({<Date = {"$(='>=' & Date(Max({<Date = {[$(='<' & Min(Date))]}, Valor = {
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