InfoPath Dev

Hello all- I was hoping someone could help me with this. S.Y.M. Wong-A-Ton wrote a great article explaining how to Filter a repeating table on a date range using rules and filters. http://www.bizsupportonline.net/infopath2003/filter-repeating-table-date-range-rules.htm

 Unfortunately, I have not found a way to contact her. I followed her instructions and they work fine. However, once the records are filtered, I am unable to correctly sum my columns, using an expression box in the footer. When I try to sum the columns, it sums all the records from the query. I would like to sum only the filtered (displayed) records.

Any suggestions would be appreciated. Thanks.  RhysAZ

 Could you show us the sum expression you have so far, and the exact filter expressions that you are using?

Hi Jimmy. There are two filters on the repeating table:     The expression

msxsl:string-compare(concat(substring(@IndexedDate, 1, 4), substring(@IndexedDate, 6, 2), substring(@IndexedDate, 9, 2)), concat(substring(../../my:startDate, 1, 4), substring(../../my:startDate, 6, 2), substring(../../my:startDate, 9, 2))) >= 0    AND

msxsl:string-compare(concat(substring(@IndexedDate, 1, 4), substring(@IndexedDate, 6, 2), substring(@IndexedDate, 9, 2)), concat(substring(../../my:endDate, 1, 4), substring(../../my:endDate, 6, 2), substring(../../my:endDate, 9, 2))) <= 0

 I am using the date pickers startDate and endDate from the hyperlinked procedure above, to filter my repeating table data, based on the IndexedDate field of my source query. 

As for the sums, I am summing the columns "Rcvd in Review", "Total Errors", etc. I wrote them as sum(@RcvdInReview), sum(@TotalErrors)  etc.

 Could you also provide a screenshot of your data source?  Sorry for not asking the first time.

Hi Jimmy- I figured it out. What I did was make an expression box in the footer, under the column I wanted to sum or average. The expression I wrote to sum my TotalErrors field in my filtered repeating table came out as:

 sum(@TotalErrors[msxsl:string-compare(@IndexedDate, startDate) >= 0 and msxsl:string-compare(@IndexedDate, endDate) <= 0])

Basically, I filtered the TotalErrors field in the expression box as:

IndexedDate is >= startDate AND
IndexedDate is <= endDate

This worked for all the other fields I wanted to sum or average.

Thanks for your help! 

 hello, would it be possible to elaborate on the structure of the function referenced above for averaging the filtered results? I'm in the same situation where I've got the date range setup as specified, works great - but have multiple counts and %'s that need to be averaged out - as of now it's just giving the cumulative figures. 

 here's what I have for an average function on the expression box

xdMath:Avg(dfs:dataFields/d:Campaigns/@Email_attempts[msxsl:string-compare(dfs:dataFields/d:Campaigns/@Date_Sent, startDate) >= 0 and msxsl:string-compare(dfs:dataFields/d:Campaigns/@Date_Sent, endDate) <= 0])