Set Analysis and variable date range

[Miguel_Angel_Baeyens]

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!

[#2]

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.

[Miguel_Angel_Baeyens]

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!

[#4 · Accepted solution]

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.

[Miguel_Angel_Baeyens]

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!

[#6]

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