3 Replies Latest reply on Jan 5, 2012 1:42 AM by weedonpaul

# I'm stuck

I'm stuck

### Post

I've done something really obviouse, but i don't know what.

I am trying to count the occurance of a word in a field by date (month and year), over a 3 month period.

in one table called coverage i create a calculation field called month year with this calculation

"(MonthName ( Date of Publication  _dd_mm_yyyy_ )  & Year ( Date of Publication  _dd_mm_yyyy_ ))"

I also create another field to count the occurance of the word "feature"

"If ( Type of Coverage = "Feature"; 1 )"

in another table called coverage report

i count the number of occurances by the if statment is to filter by companie and in this case all occurances in october (Month1)

"If ( (Coverage::Customer = Company) and (Month1 = Coverage::month year); Sum(Coverage::FeatureCounter );0)"

i do exactly the same line in 2 other fields but replace Month1 with Month2 and Month3 (November and December)

the problem is that the count is not right, I should get 2,1,1 respectivly but I get 0,0,4

Why is it counting them all in the december date? I can swap the order of the month1,2,3 so that December is 2 or 1 and december always has the total.

The only thing I can think of is that it has something to do with the fact that december is the date of the last entry in table "coverage"

• ###### 1. Re: I'm stuck

Whenever a calculation references data in a different table, the relationship linking the two tables will control the results returned by the calculation.

If I remember from your earlier thread correctly, you have this relationship between customer and coverage:

customer::CustomerID = Coverage::CustomerID

From the syntax shown, I think your calculation is defined in the Customer table. If so, Coverage:customer, and Coverage::month year will refer to the "first" related record in coverage, not a group of records specified by month and year. You'd have to build such matching into the relationship used by the calculation before you could do that. The Sum Function will then return the sum of FeatureCounter for all related records in Coverage, not the ones with the specified month. Thus this calculation either sums all related coverage records or none of them depending on whether the expression at the start of the If function evaluates as True or False.

• ###### 2. Re: I'm stuck

Thanks for that!

Could i, instead of "If ( Type of Coverage = "Feature"; 1 )" in the coverage table, write "If ( Type of Coverage = "Feature"; month year )" which would write down the month and year only when the coverage is a feature? I could then, and I may need your help to do this, do some sort of count if statment in my table "coverage report"

what do you think?

• ###### 3. Re: I'm stuck

ValueCount( FilterValues( List( coverage::Featurecount); Month1)) worked well as a count if