4 Replies Latest reply: Jun 3, 2014 3:13 PM by Rodrigo Lauro

# Qlikview Conditional Expression - Summary

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

• ###### Re: Qlikview Conditional Expression - Summary

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

• ###### Re: Qlikview Conditional Expression - Summary

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)

• ###### Re: Qlikview Conditional Expression - Summary

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)

• ###### Re: Qlikview Conditional Expression - Summary

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?