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: 
bimartingo
Contributor III
Contributor III

Set or Let?

Hi, i'm desesperated with this variables!!

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

if i use LET for a variable :

LET vExercicioRef=MakeDate(2011,2,1) ;

the expression date($(vExercicioRef)) returns 12/31/1899

and when use SET

LET vExercicioRef=MakeDate(2011,2,1) ;

the result is right: it returns 2/28/2011

Why?

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi,

What is happening in your expression not using Floor() -and sounds kind of buggy to me-, is that LET evaluates first the right part, then stores, so the logic steps that QlikView does according to your example are

LET vExercicioRef = MakeDate(2011, 2 ,1); // your sentence

// QlikView does

MakeDate(2011, 2, 1) = 01/02/2011

01 / 02 / 2011 = 0,000248632

// So

Date($(vExercicioRef)) = Date(0.000248632) = 30/12/1899

And that is because you are evaluating twice. The first when using LET (creates the date), the second when using $() (dividing the day into the value of month into the value of year). If you use instead

Date(vExercicioRef)

The result will be as expected.

Hope that sheds some light.

Miguel

View solution in original post

7 Replies
lironbaram
Partner - Master III
Partner - Master III

when you use

set before a variable it would put the text into the variable

so in your case the variable would be

vExercicioRef=MakeDate(2011,2,1) ;

if you put let before the variable it would calculate the formula and put the value to tha varaible

so in your case

let  vExercicioRef=MakeDate(2011,2,1) ;

the result will be

vExercicioRef = 01/02/2012

stephencredmond
Luminary Alumni
Luminary Alumni

Hi,

Use this:

LET vExercicioRef=Floor(MakeDate(2011,2,1)) ;

Then the value in the variable will be the integer date serial and this tends, in my experience, to be much easier to use in a variable.

Regards,

Stephen

bimartingo
Contributor III
Contributor III
Author

Stephen,

very useful information, my previous experience had teach me that floor or int are very useful for removing the time part of datetime values. It's like an universal solution , independent of technology.

But, it seems to me that the answer for my question is: when we use calculated expressions, LET is better. SET works more like a constant/literal expression declaration.

So is my understanding.

Miguel_Angel_Baeyens

Hi,

What is happening in your expression not using Floor() -and sounds kind of buggy to me-, is that LET evaluates first the right part, then stores, so the logic steps that QlikView does according to your example are

LET vExercicioRef = MakeDate(2011, 2 ,1); // your sentence

// QlikView does

MakeDate(2011, 2, 1) = 01/02/2011

01 / 02 / 2011 = 0,000248632

// So

Date($(vExercicioRef)) = Date(0.000248632) = 30/12/1899

And that is because you are evaluating twice. The first when using LET (creates the date), the second when using $() (dividing the day into the value of month into the value of year). If you use instead

Date(vExercicioRef)

The result will be as expected.

Hope that sheds some light.

Miguel

Anonymous
Not applicable

Migel,

To avoid this behavior, I often use single quotation marks.  Notice the differnece:
date(02/09/2012) returns 12/30/1899
date('02/09/2012') returns 02/09/2012
So, the safe way to use variable cretated by LET is this:
Date('$(vExercicioRef)')

Regards,
Michael

Miguel_Angel_Baeyens

Hi Michael,

Thanks for sharing. I was only showing how QlikView deals with dates. I always use the no-quotes no-dollar-expansion when the variable has been assigned with LET, and I don't need to evaluate twice the results. It makes more sense to me and makes the code cleaner. By the way, I always try to use Date#(something, 'format') instead of MakeDate() whenever possible, but that's another story

Regards.

Miguel

stephencredmond
Luminary Alumni
Luminary Alumni

Hi,

SET is, exactly as you say, a literal declaration rather than an evaluation.

SET v1 = 1+1;

->  v1 will have the value 1+1

LET v1 = 1+1;

->  v1 will have the value 2.

I will almost exclusively use LET with variables.  Unless I need to create a macro function:

http://qliktips.blogspot.com/2011/03/parameters-in-dollar-expansion.html

For dates, I always use Floor because it means that I don't need to worry about the single quotes when using it and I don't need to try and re-convert it again.  E.g.:

   Where DateField > $(vDateStart)

Regards,


Stephen