topic Re: Problem with date variable in set analysis. in QlikView

Problem with date variable in set analysis.

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Problem with date variable in set analysis.

felipedl — Thu, 18 May 2017 14:52:30 GMT

Most likely when you put the variable, the content of the variable isn't exactly as the hardcoded '12/01/2017' and in that case the set analysis won't find the equivalent date.

Felipe.

Re: Problem with date variable in set analysis.

Anonymous — Thu, 18 May 2017 14:56:28 GMT

Hi there,

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

Thanks

Re: Problem with date variable in set analysis.

trdandamudi — Thu, 18 May 2017 15:05:28 GMT

It looks like you are missing the closing quote:

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

Re: Problem with date variable in set analysis.

Thu, 18 May 2017 15:09:16 GMT

Yes indeed, Thirumala, you are right, but only on my post (was just a typo). I actually have it well written on my dashboard, and it does not work : S . Thanks anyway!

Re: Problem with date variable in set analysis.

Thu, 18 May 2017 15:11:53 GMT

Hi, Matthew. That's the very same set analysis, but with the missing closing quote. I wrote it OK on my dashboard, but I miswrote it on my post, sorry. Anyway, it does not work even with the closing quote. Thanks, anyway!

Re: Problem with date variable in set analysis.

Anonymous — Thu, 18 May 2017 15:14:08 GMT

I think I've found the solution, there should be no quotes!

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

Re: Problem with date variable in set analysis.

felipedl — Thu, 18 May 2017 15:14:24 GMT

See the output of your variable in a text box, to see if it matches your hardcoded date.

Re: Problem with date variable in set analysis.

trdandamudi — Thu, 18 May 2017 15:24:16 GMT

Ok got it.. Make sure you set the format of the date in the calendar object like below:

Re: Problem with date variable in set analysis.

Thu, 18 May 2017 15:25:24 GMT

Hi, Felip. Thanks for your answer. I agree with your observation: the content of the variable is not '12/01/2017', indeed. It is in another format (in point of fact, it is '42747').

What strikes me the most, is that I have always done this comparisons (with the same database format) and I've never run into this problem.

Anyway, I tried declaring a variable DATE($(vFechaHastaLM)) which actually shows 12/01/2017, and I tried using it in the set analysis, in the very same way I used the other, but I had no luck.

Thanks anyway!

Re: Problem with date variable in set analysis.

Thu, 18 May 2017 15:27:23 GMT

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

Re: Problem with date variable in set analysis.

Thu, 18 May 2017 15:30:35 GMT

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.

Re: Problem with date variable in set analysis.

Anonymous — Thu, 18 May 2017 15:33:01 GMT

Last attempt,

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

Re: Problem with date variable in set analysis.

Thu, 18 May 2017 15:49:16 GMT

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

Re: Problem with date variable in set analysis.

vhespinog — Thu, 18 May 2017 17:51:56 GMT

try this

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

see the attached

Re: Problem with date variable in set analysis.

hcabrera — Thu, 18 May 2017 20:57:02 GMT

Hi,

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

Re: Problem with date variable in set analysis.

Anonymous — Thu, 18 May 2017 21:03:17 GMT

you need to format your variable as

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

Re: Problem with date variable in set analysis.

Anonymous — Fri, 19 May 2017 07:29:05 GMT

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

Re: Problem with date variable in set analysis.

vardhancse — Fri, 19 May 2017 08:09:38 GMT

Hi,

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

Re: Problem with date variable in set analysis.

lawrenceiow — Wed, 24 May 2017 11:07:58 GMT

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