Qlikview Conditional Expression - Summary

[#1]

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

[#2]

I meant to say if could declare a variable and set the max date value in script and use it expression.

[Gysbert_Wassenaar · Accepted solution]

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)

[#4]

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)

[#5]

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?