Controls

Re: Filter a repeating table on a date range using rules and filters

Visfire — Wed, 05 May 2010 20:30:18 GMT

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])

Re: Filter a repeating table on a date range using rules and filters

RhysAZ — Wed, 09 Sep 2009 15:26:33 GMT

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!

Re: Filter a repeating table on a date range using rules and filters

Jimmy — Tue, 08 Sep 2009 22:35:00 GMT

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

Re: Filter a repeating table on a date range using rules and filters

RhysAZ — Tue, 08 Sep 2009 21:23:57 GMT

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.

Re: Filter a repeating table on a date range using rules and filters

Jimmy — Tue, 08 Sep 2009 20:02:13 GMT

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

Filter a repeating table on a date range using rules and filters

RhysAZ — Tue, 08 Sep 2009 16:00:04 GMT

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