topic Sum And Today (2) in QlikView

Sum And Today (2)

Fri, 26 Mar 2010 15:16:20 GMT

Hi there,

I have a field Called Closed. To calculate the number of closed calls I use SUM(IF(Closed,1,1)) However how would i get the Expression to only look at items that have been closed today?

The field that controls the date is DateUdpated, is it possible to get the expression to only show those calls that have been closed today without applying a separate filter?

Many thanks

Gerard

Sum And Today (2)

vgutkovsky — Fri, 26 Mar 2010 15:19:24 GMT

Sure, just do it like this: sum(if(Closed and date(DateUpdated)=today(),1)). This also encloses the DateUpdate in a date() function to make sure it compares the dates in the proper format.

Regards,

Sum And Today (2)

justinasp — Fri, 26 Mar 2010 15:24:09 GMT

Try this

COUNT({<Closed=1, DateUpdated=Today()>} Closed)

or on QV8

COUNT(IF(Closed=1 AND DateUpdated=Today(), Closed))

Regards,

Justinas

Sum And Today (2)

vgutkovsky — Fri, 26 Mar 2010 16:39:45 GMT

Not a bad idea if you want to calculate on the fly--my previous post assumes that the calculation will occur in the script.

Regards,

Sum And Today (2)

Fri, 26 Mar 2010 18:42:41 GMT

Thanks for the quick reply. I am usign qlikview 9. The sum of closed isnt performed in the script.

Using the above it is unable to locate the field Today?

Many thanks

Gerard

Sum And Today (2)

johnw — Fri, 26 Mar 2010 20:41:39 GMT

Yeah, I believe that syntax only works with fields. Today() isn't a field, so it failed. I think you need this:

count({<Closed={1}, DateUpdated={'$(=today())'}>} Closed)

You might also need to wrap today() in a date() function to match the format of DateUpdated. QlikView isn't smart enough to do a date comparison here, and is doing something closer to a text comparison.

Sum And Today (2)

Tue, 30 Mar 2010 12:18:47 GMT

Many thanks for all the replies. Still no joy but have a better understanding so will investiagte further. Many thanks Gerard