Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Re: Problem with date variable in set analysis.

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

Not applicable

Re: Problem with date variable in set analysis.

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.

mattquinnterex
Contributor II

Re: Problem with date variable in set analysis.

Last attempt,

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

Not applicable

Re: Problem with date variable in set analysis.

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

vhespinog
New Contributor III

Re: Problem with date variable in set analysis.

Hi

try this

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

see the attached

hcabrera
New Contributor III

Re: Problem with date variable in set analysis.

Hi,

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

atkinsow
Valued Contributor II

Re: Problem with date variable in set analysis.

you need to format your variable as

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

mattquinnterex
Contributor II

Re: Problem with date variable in set analysis.

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
Valued Contributor III

Re: Problem with date variable in set analysis.

Hi,

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

lawrenceiow
Contributor II

Re: Problem with date variable in set analysis.

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