Problem with date variable in set analysis.

[#1]

Hello, everyone.

The following (hardcoded) set analysis works as intended:

=SUM({ $ < Date = {'<=12/01/2017'} >} Amount )

But indeed what I wanna do, is let the user select the date. And so did I. I created a calendar, and assigned a variable.

But when I rewrite the set analysis, in order to include this variable

=SUM({ $ < Date = {'<=$(vFechaHastaLM)} >} Amount )

It just doesn't work. While I should get the same values as in the first set analysis. I actually don't get any value.

Does anyone have any idea of what might be happening?

Thanks!

[#2]

Hi, Matthew. There should always be quotes. It throws syntax expression error, otherwise. Thanks for the effort, though!

[#3]

Yes, indeed. I had it set like that. Anyway, that is the format used for the calendar, but I has no impact in the variable content (and thus, in the set analysis expression) whatsoever. Thanks a lot, anyway.

[Anonymous]

Last attempt,

SUM({$<Date={'<=$(=$(vFechaHastaLM))'}>}Amount)

[#5]

Unsuccessful : (   But you were doing good. Thanks, bro.

[vhespinog]

Hi

try this

=sum({$<Date= {"<=$(vFechaHastaLM)"} >}Amount)

see the attached

[hcabrera]

Hi,

Try  =SUM({< Date = {"<=$(=vFechaHastaLM)"} >} Amount )

[Anonymous]

you need to format your variable as

date($(vFechaHastaLM),'M/D/YYYY')

[Anonymous]

I had a good search online and found this, I think it is what you are looking for.

SUM({$<Date={'<=$(=vFechaHastaLM)'}>}Amount)

​Hopefully that works for you lol

[vardhancse]

Hi,

Can can take a screen shot of variable expression from variable Variable overview.

[lawrenceiow]

mpineiro‌, I've been struggling with a similar problem to you and have finally got a solution that works for me.

I have a Calendar object using my variable Dt and in the Number tab I used the Format Pattern DD/MM/YYYY

I have selected 15/05/2017 using the Calendar object and Variable Overview shows this variable to be 42870

In my case I'm using Count against a unique key SeqNo so...

• My original expression: =Count({<EndDate={'15/05/2017'}>} SeqNo)

Now, we both know that we need to replace the 15/05/2017 part with $(Dt) so we get...

• =Count({<EndDate={'$(Dt)'}>} SeqNo)

We know this does not work as Dt is 42870 so we need to format it using Date($(Dt)). In order to get the Date function to work inside the set analysis we need to use dollar expansion  $(=Date($(Dt)))

So the final expression becomes...

• =Count({<EndDate={'$(=Date($(Dt)))'}>} SeqNo)

So we're dollar expanding the Dt variable and we're dollar expanding the Date expression.

I hope that works for you.

Regards