CODEWITHSUNDEEP
xslt-2.0
Alexis Wilke
Alexis Wilke

Reputation: 20818

XSLT to compute a weighted average

Wondering whether this is possible with XSLT...

There is the data I'm dealing with. We see a set of rows (I show just one row here) with fields. Fields represent a type of sales. For example, boucherie. Each type has daily sales and a number of sales.

<?xml version="1.0"?>
<snap-report>
    <row>
        <field type="boucherie" group="sales" class="sales">
            <day date="20140801"><amount>123.22</amount><count>3</count></day>
            <day date="20140802"><amount>23.29</amount><count>5</count></day>
            <day date="20140803"><amount>32.24</amount><count>2</count></day>
            <day date="20140804"><amount>53.72</amount><count>10</count></day>
            <day date="20140805"><amount>57.12</amount><count>7</count></day>
            <day date="20140806"><amount>133.46</amount><count>12</count></day>
            <day date="20140807"><amount>253.00</amount><count>20</count></day>
        </field>
        <field type="legumes" group="sales" class="sales">
            <day date="20140801"><amount>23.22</amount><count>3</count></day>
            <day date="20140802"><amount>55.09</amount><count>5</count></day>
            <day date="20140803"><amount>132.24</amount><count>2</count></day>
            <day date="20140804"><amount>5.70</amount><count>1</count></day>
            <day date="20140805"><amount>205.07</amount><count>18</count></day>
            <day date="20140806"><amount>50.32</amount><count>2</count></day>
            <day date="20140807"><amount>93.72</amount><count>11</count></day>
        </field>
        <field type="dessert" group="sales" class="sales">
            <day date="20140801"><amount>145.23</amount><count>17</count></day>
            <day date="20140802"><amount>3.29</amount><count>1</count></day>
            <day date="20140803"><amount>302.04</amount><count>23</count></day>
            <day date="20140804"><amount>59.11</amount><count>11</count></day>
            <day date="20140805"><amount>35.72</amount><count>7</count></day>
            <day date="20140806"><amount>50.82</amount><count>3</count></day>
            <day date="20140807"><amount>67.02</amount><count>5</count></day>
        </field>
    </row>
</snap-report>

We want to compute a number representing an average amount per sale for each day available in the data. It is assumed that all the field tags have the same days as each others (I trust the source of that XML.) This is easy enough and I got a couple of templates as follow:

<?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                              xmlns:xs="http://www.w3.org/2001/XMLSchema"
                              xmlns:fn="http://www.w3.org/2005/xpath-functions"
                              xmlns:snap="snap:snap">

    <!-- some special variables to define the theme -->
    <!-- xsl:variable name="layout-name">finball</xsl:variable>
    <xsl:variable name="layout-area">report-table</xsl:variable>
    <xsl:variable name="layout-modified">2014-08-02 16:45:29</xsl:variable -->

    <xsl:template name="cell">
        <xsl:param name="date"/>

        <xsl:variable name="sales_total_amount" select="sum(../../field[@group='sales']/day[@date=$date]/amount)"/>
        <xsl:variable name="sales_total_count" select="sum(../../field[@group='sales']/day[@date=$date]/count)"/>
        <xsl:text>$</xsl:text><xsl:value-of select="$sales_total_amount div $sales_total_count"/>
    </xsl:template>

    <xsl:template match="row">
        <tr>
            <td>Average Income</td>
            <!-- use one list of days, we only need that from the first field
                 we expect the lists to always be sorted so not sort in XSLT -->
            <xsl:for-each select="field[1]/day">
                <td>
                    <!-- sum up all the fields here -->
                    <xsl:call-template name="cell">
                        <xsl:with-param name="date" select="@date"/>
                    </xsl:call-template>
                </td>
            </xsl:for-each>

<!-- this cell is the one I have a problem with... -->
            <td class="average">
                <xsl:variable name="sales_total_amount" select="sum(field[@group='sales']/day/amount)"/>
                <xsl:variable name="sales_total_count" select="sum(field[@group='sales']/day/count)"/>
                <xsl:text>$</xsl:text><xsl:value-of select="$sales_total_amount div $sales_total_count"/>
            </td>


        </tr>
    </xsl:template>

    <xsl:template match="snap-report">
        <table class="report">
            <xsl:apply-templates select="row"/>
        </table>
    </xsl:template>
</xsl:stylesheet>

We see that the math goes something like this (in pseudo code):

sum(.sales) / sum(.count)

Total in sales for that day, from each field, divided by the total number of items sold. The output is as expected for that part.

However, the last cell in the row represents an average of the previous cells. So in pseudo code, it would be something like:

sum(td) / count(td)

The sum of the numbers in the previous cells divided by the number of cells. (In truth we have to consider the special case of an entry which is empty, set to zero, but I think I'll be able to deal with that in time.)

However, the current math I use computes the wrong average:

 sale[1] + sale[2] + sale[3] ...
 --------------------------------
 cnt[1]  + cnt[2]  + cnt[3]  ...

Because the average expected is:

 td[1]    td[2]    td[3]
 ------ + ------ + ------ + ...
 cnt[1]   cnt[2]   cnt[3]
 -------------------------------
                   n

Where 'n' is the number of valid days in that row.

Those numbers (td[1] / cnt[1]) are those we already properly compute for the first few cells in that table. Unfortunately, that total, as far as I can tell, is lost to XSLT by the time we reach the last cell...

There is my current output, and the

<table class="report">
    <tr>
        <td>Average Income</td>
        <td>$12.681304347826085</td>
        <td>$7.424545454545455</td>
        <td>$17.27851851851852</td>
        <td>$5.387727272727273</td>
        <td>$9.309687499999999</td>
        <td>$13.799999999999999</td>
        <td>$11.492777777777778</td>
        <td class="average">$11.337142857142856</td>
    </tr>
</table>

The last entry, $11.3371... is wrong. The correct number should be $11.0535087. Depending on the numbers I use, the difference varies greatly, but anyway you can see that the math is incorrect as shown earlier.

The question is, is there an easy way to compute the correct average in XSLT 2.0?

Upvotes: 2

Views: 361

Answers (1)

Martin Honnen
Martin Honnen

Reputation: 167716

With XSLT 2.0 you can easily store computation results in sequences or in temporary trees:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="xs"    
    version="2.0">

    <xsl:output method="html" indent="yes"/>

    <xsl:template match="row">
        <tr>
            <td>Average Income</td>
            <!-- use one list of days, we only need that from the first field
                 we expect the lists to always be sorted so not sort in XSLT -->
            <xsl:variable name="sales-by-day">
              <xsl:for-each-group select="field[@group = 'sales']/day" group-by="@date">
                <sales day="{current-grouping-key()}">
                  <amount><xsl:value-of select="sum(current-group()/amount)"/></amount>
                  <count><xsl:value-of select="sum(current-group()/count)"/></count>
                </sales>
              </xsl:for-each-group>
            </xsl:variable>
            <xsl:for-each select="$sales-by-day/sales">
                <td>
                    <xsl:text>$</xsl:text><xsl:value-of select="amount div count"/>
                </td>
            </xsl:for-each>

<!-- this cell is the one I have a problem with... -->
            <td class="average">
                <xsl:text>$</xsl:text><xsl:value-of select="sum($sales-by-day/sales/(amount div count)) div count(field[@group = 'sales'][1]/day)"/>
            </td>


        </tr>
    </xsl:template>

    <xsl:template match="snap-report">
        <table class="report">
            <xsl:apply-templates select="row"/>
        </table>
    </xsl:template>
</xsl:stylesheet>

I think the above could then be shortened to

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="xs"    
    version="2.0">

    <xsl:output method="html" indent="yes"/>

    <xsl:template match="row">
        <tr>
            <td>Average Income</td>
            <!-- use one list of days, we only need that from the first field
                 we expect the lists to always be sorted so not sort in XSLT -->
            <xsl:variable name="sales-by-day">
              <xsl:for-each-group select="field[@group = 'sales']/day" group-by="@date">
                <sales day="{current-grouping-key()}">
                  <xsl:value-of select="sum(current-group()/amount) div sum(current-group()/count)"/>
                </sales>
              </xsl:for-each-group>
            </xsl:variable>
            <xsl:for-each select="$sales-by-day/sales">
                <td>
                    <xsl:text>$</xsl:text><xsl:value-of select="."/>
                </td>
            </xsl:for-each>

<!-- this cell is the one I have a problem with... -->
            <td class="average">
                <xsl:text>$</xsl:text><xsl:value-of select="sum($sales-by-day/sales) div count($sales-by-day/sales)"/>
            </td>


        </tr>
    </xsl:template>

    <xsl:template match="snap-report">
        <table class="report">
            <xsl:apply-templates select="row"/>
        </table>
    </xsl:template>
</xsl:stylesheet>

Upvotes: 1

Related Questions