topic Set Analysis with a Date field is not working properly in QlikView

Set Analysis with a Date field is not working properly

Tue, 07 Dec 2010 04:07:01 GMT

I have this Set Analysis that should show the Amount for today. This is just an example, what I need is a little bit more complex, but if I resolve this, I can resolve the other issues.

Sum({$<Date = {"=$(#=Now())"} >} Amt)

However, instead of show the amount for Today, it shows the total amount, ignoring the Set Analysis.

I've tried different many things like using the Date function, converting to number, etc, but they all return the same thing.

Any guess? Any idea can help.

Thanks.

Set Analysis with a Date field is not working properly

pover — Tue, 07 Dec 2010 04:23:58 GMT

Use

Sum({$<Date = {"=$(=Today())"} >} Amt)

Now() returns a timestamp and not a date. Also don't use Now() unless really necessary as it will refresh every second using system resources and cause some problems for the user experience.

Regards.

Set Analysis with a Date field is not working properly

Tue, 07 Dec 2010 04:25:54 GMT

maybe because the function Now() returns a date and a time thats why its not giving you the correct result and try to check the syntax of your expression..

Set Analysis with a Date field is not working properly

Tue, 07 Dec 2010 04:32:41 GMT

Thanks guys, but I got the same results if I change to Today().

Set Analysis with a Date field is not working properly

Tue, 07 Dec 2010 04:37:14 GMT

Interesting that I just check that Today() is returning 12/30/1899

Set Analysis with a Date field is not working properly

Tue, 07 Dec 2010 04:40:29 GMT

Nevermind. I fix the Today function but I still get the same result.

Set Analysis with a Date field is not working properly

pover — Tue, 07 Dec 2010 19:19:17 GMT

Try the following:

=sum({$<Date={"=Date=today()"}>} Sales)

I usually play with set analysis doing advanced searches over a list box of the field until it gets the result I want.

Regards.

Set Analysis with a Date field is not working properly

colinh — Tue, 07 Dec 2010 19:33:20 GMT

I find Set Analysis can be easier if you use a variables. So I would create a variable called vToday = date(today()). Your set expression would look like this:

=sum({$<Date={"$(#vToday)"}>} Sales)

Then if you need to use a different date in the future you can just change the variable, you don't need to change your set expression.