Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Highlighted
Partner
Partner

Re: Problem with date variable in set analysis.

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.

mattquinnterex
Contributor II

Re: Problem with date variable in set analysis.

Hi there,

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


Thanks

trdandamudi
Honored Contributor II

Re: Problem with date variable in set analysis.

It looks like you are missing the closing quote:

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

Not applicable

Re: Problem with date variable in set analysis.

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!

Not applicable

Re: Problem with date variable in set analysis.

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!

mattquinnterex
Contributor II

Re: Problem with date variable in set analysis.

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

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

Partner
Partner

Re: Problem with date variable in set analysis.

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

trdandamudi
Honored Contributor II

Re: Problem with date variable in set analysis.

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

Not applicable

Re: Problem with date variable in set analysis.

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!