topic Set Analysis and variable date range in QlikView

Set Analysis and variable date range

Miguel_Angel_Baeyens — Wed, 18 Nov 2009 19:33:30 GMT

Hello,

I'm using

Sum({<VTO_TIPO_VENCIMIENTO={'Cobros'}, VTO_TIPO -= {'Vto. Cobro Posterior'}, VTO_FECHA_VENCIMIENTO = {'<=$(vFechaReferencia1)'} >} IF(VTO_FECHA_VENCIMIENTO >= Date([Fecha Información Saldos]), VTO_DEUDA))

and returns the results I want to get. But I was wondering if there is any other way to include all dates in between to get the Sum propertly only with Set Analysis, I mean, using Set Analysis to say "when VTO_FECHA_VENCIMIENTO BETWEEN vFechaReferecia AND [Fecha Información Saldos]".

I've tried

Sum({<VTO_TIPO_VENCIMIENTO={'Cobros'}, VTO_TIPO -= {'Vto. Cobro Posterior'}, VTO_FECHA_VENCIMIENTO = {'<=$(vFechaReferencia1)>=$(#=Date([Fecha Información Saldos]))'} >} VTO_DEUDA))

but it doesn't seem to work (returns different results).

Maybe the above code is correct and properly formatted for best performance in QlikView?

Thanks in advance!

Set Analysis and variable date range

Thu, 19 Nov 2009 15:26:10 GMT

Hi Miguel,

I've attached a file where I make an example with your information, I hope it will be what you are expecting.

Best regards.

Set Analysis and variable date range

Miguel_Angel_Baeyens — Thu, 19 Nov 2009 15:52:45 GMT

Hello Namesake!

Thanks very much for the effort.

Your example is indeed working fine but, maybe I made myself not enough clear when I addressed the problem. I do know how to make ranges in Set Analysis, the problem appears when the range depends on two variable dates, one is contained in a field [Fecha Información Saldos] which, moreover, I have to convert with Date(), so

$(#=Date([Fecha Información Saldos]))

is what's not working in my code. In yours, you use a variable with spaces in the name, that's not the case. As you can see, my second line of code in the question is very similar to yours, but not working, because I need to include the Date() conversion.

Tried that with your example doesn't work, either. So the question remains: How can I build a range of dates when dates are contained in fields or variables which require some function to get date properly (Date, timestamp#, etc).

Thanks again!

Set Analysis and variable date range

Thu, 19 Nov 2009 18:28:44 GMT

Hi Miguel,

I suppose the variable contained in a field is a InputField, so I've sent you a modified example using inputfields, your Fecha Información Saldos variable and using my own inputfield variable.

The set analysis sentence Date([Fecha Información Saldos]) works for me using variables without spaces. Look the internal variable conversion from Fecha Informacion Saldos to Fecha_Informacion_Saldos like Date([Fecha Informacion Saldos]).

I hope it works for you.

Best regards.

Set Analysis and variable date range

Miguel_Angel_Baeyens — Thu, 19 Nov 2009 23:43:53 GMT

Hi Miguel,

Although I'm not using [Fecha Información Saldos] from an inputfield but from an excel sheet, your example works fine for me.

Many thanks!

Re: Set Analysis and variable date range

Wed, 04 Jul 2012 10:57:22 GMT

This worked great for me as well. Thanks for the advice... and thakyou Qlik community!