topic Re: Correct Expression help please? in QlikView

Correct Expression help please?

Tue, 25 Feb 2014 14:08:22 GMT

HI,

I need to calculate the count of ID which have Scheduledate where Scheduledate not equal to NULL.

can anyone help me with expression

I used below expression but it seems not getting data correct values

If(Scheduledate,Count(Distinct(ID)))

Re: Correct Expression help please?

Nicole-Smith — Tue, 25 Feb 2014 14:10:00 GMT

Try:

Count({<Scheduledate={'*'}>}Distinct ID)

Or:

Count({<Scheduledate={'?*'}>}Distinct ID)

Re: Correct Expression help please?

Clever_Anjos — Tue, 25 Feb 2014 14:10:17 GMT

Maybe

Count(Distinct {<Scheduledate={">01/01/1900"}>}ID )

can help you

Re: Correct Expression help please?

Tue, 25 Feb 2014 14:15:05 GMT

Thanks. I missed here one thing. Actually I need to get the count of IDs whihc have Scheduledate morethan date

i,e today the date is 25/02/2014. I need to show the count of IDs where Scheduledate> 25/02/2014. like the very same way if I pull the report suppose on 28/02/2014 I need to show count of IDs where Scheduledate> 28/02/2014

Is it possible?

Re: Correct Expression help please?

MK_QSL — Tue, 25 Feb 2014 14:16:58 GMT

=COUNT{<Scheduledate -= {''}>}DISTINCT ID)

Re: Correct Expression help please?

Nicole-Smith — Tue, 25 Feb 2014 14:17:20 GMT

Clever Anjos's response should solve that.

Re: Correct Expression help please?

Clever_Anjos — Tue, 25 Feb 2014 14:18:21 GMT

Count(Distinct {<Scheduledate={">25/02/2014"}>}ID )

Re: Correct Expression help please?

mvanlutterveld — Tue, 25 Feb 2014 14:19:10 GMT

Hi,

I would solve this by using a flagfield and set analysis:

Script: If(Isnull(ScheduleDate,0,1) as FlagScheduleDate

Expression: Count({<FlagScheduleDate = {1} >} ID)

If you have a 1:1 relationship between schedule date and ID ánd the ID's are distinct, you can also use Sum(FlagScheduleDate) as an expression.

Cheers,

Michiel

Re: Correct Expression help please?

Tue, 25 Feb 2014 14:19:26 GMT

Thanks. when I used this it is showing garbage after expression "{"

Re: Correct Expression help please?

Tue, 25 Feb 2014 14:24:31 GMT

may be the expression is

If(Isnull(ScheduleDate),0,1) as there is a typo mistake in your expression in above reply

Re: Correct Expression help please?

mvanlutterveld — Tue, 25 Feb 2014 14:30:49 GMT

Yep, you're correct.

Re: Correct Expression help please?

Tue, 25 Feb 2014 14:35:37 GMT

Can you try:

Count(Distinct {<Scheduledate={"$(= '>=' & Today() & '<=' & Today())"}>}ID )

THanks

Re: Correct Expression help please?

SunilChauhan — Tue, 25 Feb 2014 14:35:55 GMT

hello ,

why not simply use below

Count(Distinct if( Isnull(Scheduledate)=0, ID))

isnull give -1 for true and 0 for false

hope this helps

Re: Re: Correct Expression help please?

MK_QSL — Tue, 25 Feb 2014 14:39:39 GMT

=COUNT(DISTINCT {<Scheduledate -= {''}>} ID)

Re: Correct Expression help please?

Tue, 25 Feb 2014 15:10:45 GMT

Thanks.