Count and IF statement in one expression

[#1]

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

[#2]

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])

[pennetzdorfer]

Two questions:

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

Regards,

Florian

[jagan]

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.

[#5]

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

[#6]

Hi,

In my script I specified it as follows:

Date

(DueDate)      as [Instalment Date],

[#7 · Accepted solution]

WRIT ACCORDING TO THIS

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

[#8]

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!