Count Where Complete Date = Create Date

[Anonymous]

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,

[pokassov · Accepted solution]

Hello!

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

[awhitfield]

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

Andy

[anbu1984]

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

[Anonymous]

This is what I need.

Cheers

[Gabriel]

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