SUM by Date Sample App

[Eric_Silver]

I have taken this sample app and used it to create a Sales Rep Summary report. SUM by Date Sample App

I am able to get everything to work correctly except the “Sum Matching Items” field. I am using the same syntax as in the sample app, but I keep getting the message below in the Expression Builder. Not sure how to get around this.

“Column Matching Items is used in a SELECT expression. It is of the wrong type. It should be a list of rows”

[Lynn]

Hi @Eric_Silver Maybe check the properties of your Matching Items column. What is the formula you are using?

[Eric_Silver]

I believe I have everything set up as the sample app does (except the field names in my app are different). Here are the formulas below:

Matching Items column: SELECT(Field Estimate[Estimate Owner],
AND([Estimate Owner] = [Estimate Owner Choice], [Status] = “4 - Sold”, [Sale Date] >= [Start Date], [Sale Date] <= [End Date]))

Count of Matching Items column: COUNT([Matching Items])

Sum of Matching Items column: SUM([Matching Items][PAR])

The Count of Matching items works fine. The Sum gives me the error I referred to in the first post.

[Steve]

Is PAR a column in the table to which Estimate Owner refers?

[Eric_Silver]

Yes, it is.

[Steve]

Eric_Silver:

“Column Matching Items is used in a SELECT expression. It is of the wrong type. It should be a list of rows”

This error indicates that the Matching Items column is not of type List of Ref. The SELECT() statement you gave:

Eric_Silver:

SELECT(Field Estimate[Estimate Owner],
AND([Estimate Owner] = [Estimate Owner Choice], [Status] = “4 - Sold”, [Sale Date] >= [Start Date], [Sale Date] <= [End Date]))

would produce a List of Estimate Owner column values. Can you confirm that the Estimate Owner column of the Field Estimate table is, in fact, the key column for that table?