Adding Columns of Format Percentage

maroli · August 17th, 2011, 07:54 AM

Hi,

First of all my apologies, if any relevant information has been missed out here. I am a Tester, so do not have much knowledge of Coding.

I have created a DataView in the SharePoint Designer.

In this DataView, One of the columns requires taking the average of Two Columns that are of format number percentage (2 decimals)
The Columns that are used to get the average are also getting values based on the calculations of other Columns.

Here is the Code:

Code:

</td>
<td class="style12" style="height: 20px">
	<xsl:choose>
		<xsl:when test="COLUMN1=0">
			<xsl:value-of select="COLUMN2 div 2"></xsl:value-of>
		</xsl:when>
		<xsl:when test="COLUMN2=0">
			<xsl:value-of select="COLUMN1 div 2"></xsl:value-of>
		</xsl:when>
		<xsl:otherwise>
			<xsl:value-of select="(COLUMN1+COLUMN2) div 2" />
		</xsl:otherwise>
	</xsl:choose>
</td>

COLUMN1 has values in different rows as: 33.33%, 100.00%, 0.00%, 0
COLUMN2 has values in different rows as: 60.00%, 100.00%, 2.00%,0

As per the above data the values in COLUMN3 should be calculated as:
COLUMN3: 46.50%, 50.00%,1.00%,0

The Problem is that I get a NaN values in the Average Calculated Column as Below:
COLUMN3: NaN, NaN, NaN,0
I am not able to figure out what wrong I am doing

Please let me know if you need any further information

samjudson · August 17th, 2011, 09:22 AM

XSLT does not know that the string "33.33%" is a number, so it is giving you an error when you divide it by two.

Also, your entire choose/when statement is redundant. If COLUMN1 or COLUMN2 equals zero then adding it to the other makes no difference.

Code:

<td class="style12" style="height: 20px">
	<xsl:value-of select="(number(translate(COLUMN1,'%',''))+number(translate(COLUMN2,'%',''))) div 2" />
</td>

maroli · August 17th, 2011, 10:17 AM

Quote:

Originally Posted by samjudson

XSLT does not know that the string "33.33%" is a number, so it is giving you an error when you divide it by two.

Also, your entire choose/when statement is redundant. If COLUMN1 or COLUMN2 equals zero then adding it to the other makes no difference.

Code:

<td class="style12" style="height: 20px">
	<xsl:value-of select="(number(translate(COLUMN1,'%',''))+number(translate(COLUMN2,'%',''))) div 2" />
</td>

Thanks a Lot Sam. This helped me out.

But When i try to use the same code to get the average value of a Column, i again get the same message, I also tries formatting the values in these column to Number, but at the bottom row i get NaN

samjudson · August 17th, 2011, 10:29 AM

Well without seeing the code you are using all I can tell you is you must be doing something wrong.

joefawcett · August 17th, 2011, 10:42 AM

It can be hard to debug XSL transformations within SharePoint. can you show a sample of the XML? If you can't do this directly then search for the identity template (which basically copies the XML unchanged). You can then get the raw XML and just develop the XSLT in isolation using the normal tools.

maroli · August 17th, 2011, 11:12 AM

Ok.. Let me try to be more clear:

In the Data View, I selected DataView Properties
Under General Tab, I enabled the Check Box - Show Column summary row

Now in the data view, navigated to the column whose overall average I need;

I used the following:

Code:

<xsl:value-of select="sum($Rows/COLUMN4) div count($Rows)" />

The values in COLUMN4 are number with 2 decimal points
But instead of getting average value, I am getting NaN.

I tried replacing count($Rows) with number(count($Rows)) and also with a total rows count i,e 17, but still no luck

samjudson · August 17th, 2011, 04:38 PM

If, like your other input data, COLUMN4 contains the % character it will not be parsed as a number by XSLT, and hence sum() will return NaN (Not A Number) for anything containing such data.