Set Analysis - Date field FRUSTRATIONS!!!

[#1]

I am getting more and more frustrated with Qlikview's handling of date fields in set analysis. All I want to do is sum up a field for values greater than a certain date. The below expression works:

=sum({< DateField = { ' >$(=Now())' } Hours) - evaluates

But when I try to do any type of calculation on current dates/times the expression will not evaluate. I find it difficult to even hard code a date into this expression.

=sum({< DateField = { ' >$(=Now()-30)' } Hours) - does not evaluate

Qlikview continues to not evaluate various expressions when it comes to date fields. Is this an issue with the system date? I have tried various ways to load this DateField in the load script and they all seem to load through as a date field.

How do I sum up fields between given time periods? I shouldn't have to do it in the load script, it should be a simple set analysis; greater than less than function.

This should not be as difficult as I am making it, so I am hoping that I am making a real simple mistake....

[#2]

In order to have set analysis work with serch criteria, like >$(=Now()) you need to enclose it in double quotes, not single quotes.

Also, if you are trying to subtract 30 HOURS from now() you need to remember that the Date/Time finctions in QV are actually working on Julian dates. (ie. 40025.4224) where the whole part is the day, and the decimal is the time (or fraction of the day). To subtract 30 hours you woule need something like

Now() - (30/24)

whereas your current expression is subtracting 30 days.

Hope this helps,

sjprows

[#3]

The first expression from my example works with either double or single quotes. Doesn't seem to matter.

The second expression does not evaluate with either. I realize that I am subtracting 30 days. I just chose a number to see if it would evaluate anything and it does not. Even using your example above gets me zero - which is impossible since I get values for the first expression???? I have tried using the functions: num, date, addmonths, and even tried a straight number. It just does not recongize anything once a calculation is applied to the date.

[#4]

Try this...

Sum( {$< DateField = { '">$( =Date( Now()-30 ) )" } Hours )

sjprows

[#5]

I seem to be fine when using single quotes. I think it comes down to your data types.

First, I would try Today() instead of Now(). Today() gives just the date, while Now() is date + time. It shouldn't matter, but I can't see anything wrong with that second expression, so I'm reaching.

A good way to test out your Set Analysis is to create a chart with your expression and don't modify the label. When the chart is rendered, the label will be your Set Analysis expression, but the dollar sign expansion will be expanded. Then you can figure out what is in your Set Modifier and that should give you an idea of what is going wrong.

I'm actually stumped on this one. Since the first expression works, passing a date should work. Now() is evaluated as a decimal and Now() - 30 should as well. It may be helpful to post a sample.

Actually, I found an issue, but it is present in both of your expressions. You're not properly closing out the Set Analysis block. You're missing the > at the end. Try this:

sum({< DateField = { ' >$(=Now()-30)' }>} Hours)

[#6 · Accepted solution]

I think that I have figured out what was happening, but not sure why. The suggestion to remove the label from the field helped tremendously. This showed me that the formula was correct and that it was creating the correct/expected results. What I found was that my date field was a time stamp type date i.e. "2009-08-16 12:00:00 AM" so it did not compute against the criteria of "2009-08-16". I tried to re-format it in my load script by forcing it to be the format "MM/DD/YYYY', but for some reason it would not change it. I discovered this by putting that date field into a list table and saw that the format did not change. It is a key field linked to another table, but I'm not sure if this is the reason the format would not change.

I ended up creating a new date field "MDate" using the same field, but not linked to any other table. Now the below seems to work. Thanks all for the help and hopefully end my date frustrations!!

=sum({$ < MDate = {'>=$(=date(Today()-35)) <=$(=date(Today()))'} >} Hours)

p.s. the single quotes seem to be what works for me in the set analysis.

[#7]

Kevin, You're a genius! I've been struggling with this for 3 days!!! Those single quotes...

Thank you,

Tony