topic Re: the count if did not work ? in Qlik Learning Discussions

the count if did not work ?

guantujiang — Thu, 15 Jan 2015 09:39:07 GMT

I would like to have a count of working day base the loaded list, so I try in the count if command,

it work if it write down the date with string,just as

=Count(if(CALENDERDATE < '15.01.2015', ISOFFICEHOLIDAY))

even work with

=Count(if(CALENDERDATE < today(), ISOFFICEHOLIDAY))

the result should be 104 ( working day)

but it will fail if I try as below

=Count(if(CALENDERDATE < DATADATE, ISOFFICEHOLIDAY))

the result show 402 or some other more.

the DATADATE was flited out by selected list in dataday & datamonth & datayear, if I try the formula in a text object

=DATADATE

it show with the right result just as : 05.10.2015

what happened??

=====================

list as below:

CALENDERDATE	ISOFFICEHOLIDAY
30.09.2014	0
01.10.2014	1
02.10.2014	1
03.10.2014	1
04.10.2014	1
05.10.2014	1

Re: the count if did not work ?

alexandros17 — Thu, 15 Jan 2015 09:41:28 GMT

Try with (Count(if(CALENDERDATE < Date(DATADATE), ISOFFICEHOLIDAY)))

the problem is in the date format ...

Re: the count if did not work ?

guantujiang — Thu, 15 Jan 2015 10:36:24 GMT

still not work,

I even try (

Count(if( num(CALENDERDATE, '0') < num(DATADATE, '0'), ISOFFICEHOLIDAY))

(Count(if(CALENDERDATE < makedate( year(DATADATE),month(DATADATE),day(DATADATE)), ISOFFICEHOLIDAY))

but neither work,

but I agrree that should be issue in format

Re: the count if did not work ?

alexandros17 — Thu, 15 Jan 2015 10:39:23 GMT

Coupd you send me your document?

Re: the count if did not work ?

guantujiang — Thu, 15 Jan 2015 12:26:58 GMT

SURE, i HAVE SENT YOU THE FILE WITH MAIL

THANKS

Re: the count if did not work ?

Anonymous — Thu, 15 Jan 2015 12:55:26 GMT

Do I read it right that the DATADATE returns 05.10.2015 (October 5 or May 10)?

In your hardcoded example it is January 15...

Re: the count if did not work ?

guantujiang — Fri, 16 Jan 2015 00:28:42 GMT

actually, the value of datadate will be change while u selete different datayear & datamonth & dataday. so it is not the key point i think

Re: the count if did not work ?

guantujiang — Fri, 16 Jan 2015 00:29:36 GMT

the datatime format is dd.MM.yyyy

Re: the count if did not work ?

guantujiang — Fri, 16 Jan 2015 02:43:04 GMT

well, finnally I get it

should be like this

= Count( DISTINCT if(CALENDERDATE < MonthEnd( DATADATE ) and CALENDERDATE >= MonthStart( DATADATE ) and ISOFFICEHOLIDAY = 0 ,CALENDERDATE ))