topic Re: Qlikview Conditional Expression - Summary in QlikView

Qlikview Conditional Expression - Summary

Fri, 23 Nov 2012 18:12:50 GMT

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

Fri, 23 Nov 2012 18:22:28 GMT

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

Gysbert_Wassenaar — Fri, 23 Nov 2012 18:31:36 GMT

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

Mon, 26 Nov 2012 14:42:06 GMT

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

Tue, 03 Jun 2014 19:13:37 GMT

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?