topic Re: count by date in QlikView

count by date

darren_dixon — Thu, 19 Sep 2013 08:57:49 GMT

Hi,

I am looking to count the number of orders which have been completed today.

I've tried the following code:

sum( {$<ClosedDate = {$(Date-Today)}>} mrID)

Variable:

Date-Today = Today()

Data

mrID ClosedDate

1 18/09/2013

2 19/09/2013

3 19/09/2013

4 19/09/2013

Thanks,
Darren

Re: count by date

tresB — Thu, 19 Sep 2013 09:06:06 GMT

Try:

sum( {$<ClosedDate = {'$(=Date(Today(),'DD/MM/YYYY'))'}>} mrID)

Re: count by date

darren_dixon — Thu, 19 Sep 2013 09:18:00 GMT

Unfortunately it returns a value of 0

Re: count by date

sathishkumar_go — Thu, 19 Sep 2013 09:20:23 GMT

Hi, In script create the flag based on, if(fabs(today()) = fabs(ClosedDate),1,0) as FLAG_NO_OF_ORDERS_CLOSED In Expression, sum(FRLAG_NO_OF_ORDERS_CLOSED) Regards Sathish

Re: Re: count by date

tresB — Thu, 19 Sep 2013 09:26:10 GMT

PFA. It works fine. The most likely issue is date format. You might want to share your sample app.

Re: count by date

Anonymous — Thu, 19 Sep 2013 09:35:08 GMT

Hi,

If your mrID is an ID, then you have to count it and not to sum it.

Otherwise if it is the number of orders then you have to sum it.

The formula should be:

If mrID is an ID:

count({$<ClosedDate = {'$(=today())'}>} mrID)

If mrID is the number of orders:

sum({$<ClosedDate = {'$(=today())'}>} mrID)

Re: count by date

darren_dixon — Thu, 19 Sep 2013 09:36:54 GMT

Hi Tresesco,

Thanks for your sample data. The date format was the issue and was resolved by date(floor(Date)).

Thanks again for your help

Re: count by date

sathishkumar_go — Thu, 19 Sep 2013 09:38:15 GMT

Hi, Don't use count function in the expression. Because it takes lot of memory. So better create the flag in the script level. And in expression use sum(Flag). Regards Sathish