Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

guantujiang
New Contributor III

the count if did not work ?

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.20140
01.10.20141
02.10.20141
03.10.20141
04.10.20141
05.10.20141

Tags (2)
1 Solution

Accepted Solutions
guantujiang
New Contributor III

Re: the count if did not work ?

well, finnally I get it

should be like this

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

8 Replies

Re: the count if did not work ?

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

the problem is in the date format ...

guantujiang
New Contributor III

Re: the count if did not work ?

still not work,

I even try (

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

or

(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 ?

Coupd you send me your document?

guantujiang
New Contributor III

Re: the count if did not work ?

SURE, i HAVE SENT YOU THE FILE WITH MAIL

THANKS

mov
Esteemed Contributor III

Re: the count if did not work ?

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...

guantujiang
New Contributor III

Re: the count if did not work ?

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

guantujiang
New Contributor III

Re: the count if did not work ?

the datatime format is dd.MM.yyyy

guantujiang
New Contributor III

Re: the count if did not work ?

well, finnally I get it

should be like this

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

Community Browser