How to filter a list on one column greater than another column

Question

_{Friday, March 30, 2012 7:27 PM}

I have a list that has the following columns

Due Date, Date Completed (Other columns are irrelevant).

I want to filter the list and show items that Date Completed > Due Date.

I was hoping I could just filter on Date Completed and then set the operation to greater than and then for the value use [Due Date] But that did not work.

In addition to this list view I also need to make a chart of late tasks per month.  The filter would be the same but I would need to group by month.  I am using Enterprise so I have access to charts but they are not as easy to use as I feel that they should be.

Any guidance in one or both of these issues would be great.

Thanks.

All replies (4)

_{Friday, March 30, 2012 7:50 PM | 1 vote}

This is easy to do using conditional formatting.  Take a look at the following article on Conditional formatting.  I think its just what you are looking for.

http://blog.sharepointrx.com/2010/12/03/conditional-formatting-and-date-comparisons-with-sharepoint-designer-2010/

Paul Stork SharePoint Server
MVP Chief SharePoint Architect: Sharesquared
Blog: http://dontpapanic.com/blog
Twitter: Follow @pstork
Please remember to mark your question as "answered" if this solves your problem.

_{Friday, March 30, 2012 8:19 PM}

From this blog it looks like what the user is doing is adding status columns depending on other values.  While this may be something that I may need eventually for this I am not sure if It answers my question.

What I would like to do is add to the List View Web Part Query this.

        <Where>
         <Gt>
          <FieldRef Name="Closed_x0020_Date"/>
          <Value Type="DateTime">
           <FieldRef Name="Current_x0020_Due_x0020_Date"/>
          </Value>
         </Gt>
        </Where>

But when I do that it doesn't work.

_{Saturday, March 31, 2012 11:26 AM}

This may not sound a good solution but just give it a thought, can you create a new calculated field which will calculate Date Completed - Due Date. Then Add a filter in your view to check if this calculated field is > 1, then only show items.

Thanks,

Bokes

_{Monday, April 2, 2012 1:17 PM}

I did not think about doing it this way.  I would prefer to do this through a query but this would at least provide a way to do this.

My other thought was to create a custom web part that would query this data then using the chart web part to connect to this web part and use it as the data source. 

It just feels like the chart web part should be able to do something this simple.