topic Re: Count and IF statement in one expression in Water Cooler

Count and IF statement in one expression

Thu, 18 Jul 2013 12:36:10 GMT

Hi there,

I am trying to do a distinct count of the number of contracts (agreement numbers) we have in May. (Instalment Date for May)

I am using the following expression, but it show zero value:

count ({<[Instalment Date]={'01/05/2013'}>}DISTINCT [Agreement Number])

What am I doing wrong?

Thanks

Rentia

Re: Count and IF statement in one expression

Thu, 18 Jul 2013 12:42:43 GMT

try this

firstly write check your date field in correct format like write your date as

DATE(date#([Instalment Date],'DD/MM/YYYY'),'DD/MM/YYYY')

count(Distinct if([Instalment Date]='01/05/2013',[Agreement Number]))

or try this

count ({<[Instalment Date]={'01/05/2013'}>}DISTINCT [Agreement Number])

Re: Count and IF statement in one expression

pennetzdorfer — Thu, 18 Jul 2013 12:44:38 GMT

Two questions:

Does Qlikview recognize [Instalment Date] as Date? (try number format "integer" to check this)
Is [Instalment Date] formatted as DD/MM/YYYY (as you wrote in your set analysis expression)?

Regards,

Florian

Re: Count and IF statement in one expression

jagan — Thu, 18 Jul 2013 12:46:18 GMT

HI,

Try like this to get value for entire may month

=count ({<[Instalment Date]={'>=$(=MakeDate(2013, 5, 1))<=$(=MonthEnd(MakeDate(2013, 5, 1)))'}>}DISTINCT [Agreement Number])

Note : Check the date formats of "Instalment Date" and MakeDate(2013, 5, 1) are in the same format, then only this expression works.

Hope this helps you.

Regards,

Jagan.

Re: Count and IF statement in one expression

Thu, 18 Jul 2013 12:51:06 GMT

Hi there,

I tried the first method, because the 2nd option you gave me is the one I was using initially.

Even the second option is not working - still gives me a zero value.

P.S. Where do I type the Date Function?

Thanks

Re: Count and IF statement in one expression

Thu, 18 Jul 2013 13:01:17 GMT

Hi,

In my script I specified it as follows:

Date

(DueDate) as [Instalment Date],

Re: Count and IF statement in one expression

Thu, 18 Jul 2013 13:03:56 GMT

WRIT ACCORDING TO THIS

DATE(DATE#(DueDate,'DD/MM/YYYY'),'DD/MM/YYYY') as [Instalment Date],

Re: Count and IF statement in one expression

Thu, 18 Jul 2013 13:08:38 GMT

Thanks Jagan,

I changed the Date format as per your reply and and after I reloaded it worked.

I am still very new to Qlikview and need all the help I can get.

Thanks so much!