Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with date variable in set analysis.

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.

Calendar.JPG

VARIABLE.JPG

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!

19 Replies
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

Last attempt,

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

Not applicable
Author

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

vhespinog
Contributor III
Contributor III

Hi

try this

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

see the attached

hcabrera
Contributor III
Contributor III

Hi,

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

Anonymous
Not applicable
Author

you need to format your variable as

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

Anonymous
Not applicable
Author

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
Specialist III
Specialist III

Hi,

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

lawrenceiow
Creator II
Creator II

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