Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I having trouble with a conditional expression in Pivot table.
The expression is
=Sum(if(REALIZED_DATE < date(max(REALIZED_DATE)) and GL_REALIZED_DATE > monthstart(addmonths(date(max(REALIZED_DATE))), -6, 1)), TRANSAMOUNT * -1, 0))
I am trying to get a summary on transamount * -1 when the realize date in within six months of max realize date.
Am I not allowed to use max within sum, if so is there a way i can load the max date into a parameter in script and use parameter for the coniditon.
Appreciate your input.
Thank you,
Pad
Try
sum({<REALIZED_DATE={'$(=date(max(REALIZED_DATE)))'},GL_REALIZED_DATE={'>$(=date(monthstart(addmonths(max(REALIZED_DATE))), -6, 1))'}>} TRANSAMOUNT * -1)
Or with variables:
vStartDate: date(max(REALIZED_DATE))
vEndDate: date(monthstart(addmonths($(vStartDate), -6, 1)))
sum({<REALIZED_DATE={'$(vStartDate)'},GL_REALIZED_DATE={'>$(vEndDate)'}>} TRANSAMOUNT * -1)
I meant to say if could declare a variable and set the max date value in script and use it expression.
Try
sum({<REALIZED_DATE={'$(=date(max(REALIZED_DATE)))'},GL_REALIZED_DATE={'>$(=date(monthstart(addmonths(max(REALIZED_DATE))), -6, 1))'}>} TRANSAMOUNT * -1)
Or with variables:
vStartDate: date(max(REALIZED_DATE))
vEndDate: date(monthstart(addmonths($(vStartDate), -6, 1)))
sum({<REALIZED_DATE={'$(vStartDate)'},GL_REALIZED_DATE={'>$(vEndDate)'}>} TRANSAMOUNT * -1)
I have used as follows...
Thank you very much for your help...
=sum({<GL_REALIZED_DATE={'<=$(=monthend(addmonths(date(today()), -1, 1)))'},GL_REALIZED_DATE={'>=$(=monthstart(addmonths(date(today()), -6, 1)))'}>} TRANSAMOUNT * -1)
Hi,
Sorry but I need some help with something pretty similar to this.
I have a range of dates, but I need some dates to be ALLWAYS
This is the expression
AVG( {$<
FechaReal={"$(= '>=' & date(Addmonths(FechaReal, -12)) & '<=' & date(FechaReal) )"}, MesAño=, Año=
> }Datos )
what I need is the variable MES = "YTD 2014" be allways. Hoy Can I do?