Get columns Totals for date column in a sharepoint list using XPath Query in sharepoint designer
Posted on 13. Nov, 2009 by admin in Articles
If you want to use totals in sharepoint list view to get the maximum/minimum/sum… or any of the totals for any column then it is easy, you just need to edit the view of your list and add the total you want based on any column.
But…. What if you need to have for example 4 filters in your list and at the same time you need to get the totals?? This is a little bit trickier since you need to do it from sharepoint designer and add the XPath Query you need since the limitation for sharepoint list is to have 2 group by columns.
Ok great, till now everything is working fine… but have u tried to make the above in sharepoint designer and use the totals to a date field?? J Well here you will face the problem since it will not work for date values but it will work fine for any other values such as numbers and strings.
In the below sample, I am trying to get the MAX date in a list which is using 4 Group by columns and here is the way to do it:
- go to your sharepoint list and create a new column called “DateNew” of type “Calculated Value” and Data type “Number”
- In the formula for the calculated Value put the following code that reference to the Date column “DateToStart” as below:
=CONCATENATE(TEXT(DateToStart,”yyyy”),TEXT(DateToStart,”mm”),TEXT(DateToStart,”dd”))
- click ok and make this column hidden from the list
- Now go to the sharepoint Designer, select where you want to insert formula and type the following:
concat(substring(ddwrt:Max($nodeset/@DateNew),1,4),’/',substring(ddwrt:Max($nodeset/@DateNew),5,2),’/',substring(ddwrt:Max($nodeset/@DateNew),7,2))
- Or if you want you can add directly the XSL code with the XPath Query as below:
<xsl:value-of select=”concat(substring(ddwrt:Max($nodeset/@DateNew),1,4),’/',substring(ddwrt:Max($nodeset/@DateNew),5,2),’/',substring(ddwrt:Max($nodeset/@DateNew),7,2))” />
- Save the page and now you will be able to get the Maximum and Minimum for dates
Good Luck and hope the above is clear 