topic Re: count if date >= AND <= than value in QlikView

count if date >= AND <= than value

Fri, 24 May 2013 11:15:05 GMT

Hey folks,

I have a question concerning dates.

I have the following table: two dimensions (type and shop)

"Amount" shows the amount of deliveries/pick up's. You find the value "amount" with the following expression:

=(Count({$<Ordernr={'40*', '9*'}>}DISTINCT Ordernr))

Ordernr is the code a delivery/pick up gets. So it just counts the number of codes starting with 40* or 9* (pick up and delivery).

BUT: I now need the amount of deliveries/pick up's for a specific month. The date can be found in the dimension "date" (DD/MM/YYYY)

How can I tell Qlikview which month it needs to take into consideration?

something like

if( date >= 01/04/2013 AND <=30/04/2013, Count({$<Ordernr={'40*', '9*'}>}DISTINCT Ordernr)))

But it does not show me any values..

So I should be able to count the amount of orders in a specific month. The "date" can not be selected as a value, cus I need to be able to count it for different months in different expressions.

Who can help me please?

Thanks alot!

Zipke

Re: count if date >= AND <= than value

Fri, 24 May 2013 11:17:03 GMT

if( date >= 01/04/2013 and date <=30/04/2013, Count({$<Ordernr={'40*', '9*'}>}DISTINCT Ordernr))

Re: count if date >= AND <= than value

Sokkorn — Fri, 24 May 2013 11:20:49 GMT

Hi Zipke,

Use this set analysis and let me know the result

Count({$<Ordernr={'40*', '9*'}, date = {">=01/04/2013<=30/04/2013"}>} DISTINCT Ordernr)

Regards,

Sokkorn

Re: count if date >= AND <= than value

Fri, 24 May 2013 11:48:11 GMT

Use set analysis but make your live easier:

create time dimensions:

Year(orderdate)

month(orderdate)

week(orderdate)

and so on.

Then just use the simple expression count(distinct ordernr) and select a month, year or week combination you want. Also you can just set the set analysis to the right monthnmbr:

Count({$<Ordernr={'40*', '9*'}, month = {'4'}>} DISTINCT Ordernr) or

Count({$<Ordernr={'40*', '9*'}, month = {"=$(vMonthNumber)"}>} DISTINCT Ordernr)

Re: count if date >= AND <= than value

Fri, 24 May 2013 12:15:33 GMT

try this

=Count({$<Ordernr = p({$<date ={">= 01/04/2013<=30/04/2013"},Ordernr={'40*','9*'}>}Ordernr)>} DISTINCT Ordernr)

******************************************************************************************************************

or try this

=Count({$<Ordernr = p({$<date ={">= 01/04/2013<=30/04/2013"},Ordernr={'40*','9*'}>})>} DISTINCT Ordernr)

Re: count if date >= AND <= than value

Fri, 24 May 2013 12:57:58 GMT

Thanks already for the replies.

I am checking the results, since some of them give me different numbers..

I have one last question though, the date is nog in DD/MM/YYYY. I changed it in my chart to number>date, but when I do not do this, it just gives me the standard numbers eg. 01/01/2013 = 46523

Does this change somehting to the expression I need to use?

Cheers,

Zipke

Re: count if date >= AND <= than value

er_mohit — Fri, 24 May 2013 13:34:14 GMT

Try this

Count( {$<date ={"$(='>=' & Date#('01/04/2013','DD/MM/YYYY') & '<='& Date#('30/04/2013','DD/MM/YYYY'))"} ,Ordernr={'40*', '9*'} >}DISTINCT Ordernr)

Re: count if date >= AND <= than value

Tue, 28 May 2013 07:33:08 GMT

if you change the date format in number then write

num(datefield) or num(date('01/04/2013'))

IF you change the num in date then write this

date(datefield) or date('01/04/2013')

if your problem resolved then marked it as either correct or helpful according to your question so that it might be helpful for other developer

Regards

vishwaranjan

Re: count if date >= AND <= than value

Tue, 28 May 2013 07:36:23 GMT

Thank you vishwaranjan,

I have been trying all the options.

And I will mark a reply as correct or helpful, don't worry, but only once I am 100% sure it is correct. It gives me values that I did not expect, so I have to check the results first, but did not have the time yet.

Cheers,

Zipke

Re: count if date >= AND <= than value

Wed, 29 May 2013 09:00:47 GMT

Thanks to all for the reactions.

The expressions of Sokkorn and er.mohit were the correct ones.

Vishwaranjan, thanks to you too. For some reason it did not work for 100%, so I prefere to use the other ones.

Cheers for the quick replies!

Zipke

Re: count if date >= AND <= than value

Wed, 29 May 2013 09:36:42 GMT

Hey Sokkorn, I am using your expression.

Just one question, it should not happen, it was by mistake, but I get values when I enter:

Count({$<Ordernr={'40*', '60*', '70*', '9*'}, Date = {">=01/03/2013<=31/01/2013"}>} DISTINCT Ordernr)

How is that possible?

Is there AND, or OR between the dates? I cannot figure out why it counts that much values.

Is it only showing me the values that equal 01/03/2013 + the values that equal 31/01/2013, is this how I should interprete this mistake?

Cheers