Setting a variable as todays date does not seem to store correct num value

[#1]

In a script, set a variable to today's date:

Let vDateToday = today();

Now, create a text object on the sheet, and enter:

=vDateToday

This displays '18/11/2011' in the text object. Great, that's what i would expect.(PS I'm in the UK, so I'm using dd/mm/yyyy).

However, if I change the text object to be:

=$(vDateToday)

i.e. evaluate the expression. This displays 0.00081370643280141. Why?? The underlying numeric value for the date would be 40865. Where does 0.0008137... come from? This is giving me trouble as I am trying to use expressions in a set analysis statement and it isn't working:

=SUM({$<FactType={'Shop sales'}, Date={">=$(vDateToday) <$(vDateOneYearAgo)"}>} Count)

Any ideas greatly appreciated.

[#2 · Accepted solution]

For some reason using $() is evaluating it as 18 divided by 11 divided by 2011. Instead of using the variable in the set couldn't you just use today()?

=SUM({$<FactType={'Shop sales'}, Date={">=$(=today()) <$(vDateOneYearAgo)"}>} Count)

Edit:

you could also try removing the $ for the variable in the set analysis.

[johnw]

The variable is probably being stored as text only.  It appears that in an input box, you can use the Number tab to define it as a date, and it will then have dual formats like any other date.  So that may be one answer.

However, your set analysis SHOULD be working either way.  Given where you're inserting this text in the set analysis, QlikView has no reason to assume you're doing a calculation there.  It is a literal insertion of the text.

I built this example to demonstrate that, and changing back and forth between text and date formats doesn't change the results.  Note that if you have a straight table with the set analysis as the first expression, the caption of the table will default to your set analysis before dollar sign expansion, and the column label will default to your set analysis AFTER dollar sign expansion.  This can be very helpful when debugging set analysis expressions.  In this case, we see that the date is correctly inserted into the set analysis, and again, whether or not it is just text or an actual date.

So I suggest doing something like I've done.  If you see a date in the label, your problem lies elsewhere.  Perhaps there are format differences?  The date formats must match for the set analysis to work since it's basically doing a text comparison instead of a date comparison.

All that said, I agree with marcsliving that just using today() should do the trick.  There seems no need to create a today variable when you have the today() function.

[#4]

marcsliving, you're a genious - I never twigged that it was doing 18 divided by 11 divided by 2011. Good spot. I have followed your suggestion and am now doing the following:

=SUM({$<FactType={'Shop sales'}, Date={"<=$(=today()) >$(=AddMonths(today(), -12))"}>} Count)

And that works like a charm. Thanks!

However I'd still like to find a way to use a date variable in a set analysis expression.For example, if you wanted to include a date in a set analysis expression which was the result of a complicated or long calculation, and you had a lot of chart objects using the expression, you would want to do the calc in the script by setting a variable to keep things clean.