2

Which, I realize, is not the greatest title in the world, but I'm not sure what else to call this.

So, the list data looks a little like this:

Sample Data

More or less. Basically, a set of products are lumped into a product group and clients are either using them or not. I have about 80+ products put into 20 or so product groups for something near 40+ clients.

What I'm trying to do is build an XSLT that shows the percent penetrated (I know, I know...) for the product group.

The output would look something like this, as based on the sample data included above:

Group 1: 33.33% penetrated

This number is defined by:

  • First, per client, within the product group, counting how many products are "in use".
  • Then, dividing that count by the total number of products, per client, to get a percentage.
  • If that percentage is over 50 (meaning, if the client is using more than half of the products in the group), count that client as "using" the product group.
  • Take that count and divide it by the total number of unique clients within a product group, to derive a percentage "using" the product group.

I've been able to pull a percentage for each individual product pretty easily (@Title is the Product):

<xsl:variable name="rowYes" select="count($Rows[@Title = current()/@Title and @InUse = 'Yes'])"/>
<xsl:variable name="allRows" select="count($Rows[@Title = current()/@Title])"/>
<xsl:variable name="percPen" select="$rowYes div $allRows"/>

Then, in the table, I just call that $percPen variable for each @Title, formatting it as a percentage.

Taking it out a level is doing my head in. To summarize:

  • Count, per unique client, per unique product group, InUse = y.
  • Divide by total number of rows, per unique client, per unique product group.
  • Test result. Count all results > .5 per unique client, per unique product group.
  • Divide by total number of results to derive percentage.

It is beyond my puny brain. Help?

Improve this question
7
  • Is there something I can add to this to make answering it a little easier?
    – ChrisPEditor
    Jun 11, 2015 at 13:25
  • Something to show the expected result might help me understand what you're trying to do. Maybe how you would do it in Excel or manually? That would help figure out how to mimic it in SP.
    – IMTheNachoMan
    Jun 11, 2015 at 15:49
  • Oh, no, the excel is there just to give you a sense of the structure of the list without breaking all of the PCI compliance and non-disclosure agreements I've signed. The data sits in a list. In answer to your question about what I'm trying to do, it'd be to output a list of the product groups with that penetration percentage I defined in my original post.
    – ChrisPEditor
    Jun 11, 2015 at 15:54
  • Yeah but I think I'm getting lost in the words. Could you show the expected result with Excel (like a table) using the example data you provided?
    – IMTheNachoMan
    Jun 11, 2015 at 15:57
  • 1
    I think you should use an xsl key to create groups. The key value would be a combination of group and client. Then looping this key you can calculate the percentages you want.
    – Jan Jouke Tjalsma
    Jun 12, 2015 at 6:00

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.