topic Re: Count Where Complete Date = Create Date in QlikView

Count Where Complete Date = Create Date

Anonymous — Mon, 27 Apr 2015 13:00:11 GMT

Hi,

I have two fields CREATE_DATE_YEARMONTH and COMPLETE_DATE_YEARMONTH. I want to create a table chart which shows total number of Products that where created Per Month and subsequently the total number that were completed.

Completed Date must equal Create date to ensure it is just counting figures relating to the same month.

I have tried:.

Count([COMPLETE_DATE_YEARMONTH]=[CREATE_DATE_YEARMONTH])

But this seems to be returning count of all created values not just those that were completed/created same month. Any ideas?

Thanks,

Re: Count Where Complete Date = Create Date

pokassov — Mon, 27 Apr 2015 13:06:29 GMT

Hello!

sum(if([COMPLETE_DATE_YEARMONTH]=[CREATE_DATE_YEARMONTH],1,0))

Re: Count Where Complete Date = Create Date

awhitfield — Mon, 27 Apr 2015 13:11:25 GMT

Can you post an example qvw? Also, what are you counting?

Andy

Re: Count Where Complete Date = Create Date

anbu1984 — Mon, 27 Apr 2015 13:17:57 GMT

=Count(Distinct If([COMPLETE_DATE_YEARMONTH]=[CREATE_DATE_YEARMONTH],Product))

Re: Count Where Complete Date = Create Date

Anonymous — Mon, 27 Apr 2015 14:15:03 GMT

This is what I need.

Cheers

Re: Count Where Complete Date = Create Date

Gabriel — Mon, 27 Apr 2015 15:06:32 GMT

Hi,

I would suggest creating extra filed in the script to define your search. Like this

LOAD

* Inline [
ID,Start_Date,Complete_Date,Product
1, 12-03-2015, 12-03-2015,ABC

2, 13-03-2015,13-03-2015,CDE

3, 22-03-2015, 28-03-2015,HGT

4, 25-03-2015, 15-04-2015,TTT

];
Data_1:
LOAD
*
,IF(Start_Date = Complete_Date,1,0) AS %KEY_Match
Resident Data;
DROP TABLE Data;

And what you can do is to have a simple expression like this in you chart

Count({<%KEY_Match = {1}>}Product) .

Hope this helps