Using COUNT for unique items only - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Using COUNT for unique items only

Last post 03-09-2016 03:02 PM by Hilary Stoupa. 4 replies.
Page 1 of 1 (5 items)
Sort Posts: Previous Next
  • 03-08-2016 01:29 PM

    Using COUNT for unique items only

     Hello I have a SharePoint library that tracks users training modules, I look at two columns Title and Module to derive the count, and want to count each unique item in Module for each user in Title. My issue is if a user takes the same training multiple times it counts towards their overall completion total which isn't accurate. For historical purposes I haven't disabled the SharePoint setting "allow duplicate values" (this will be changed in the future but for now I need to work with what I have) so:

    [Title]  [Module]

    Joe     How to make a Form

    Joe     How to make a Form

    Joe     How to publish a Form

     

    In the above example Joe has assigned two courses but he complete the same one twice, when my current form counts its displays Joes percentage compete as 3/2 or 150%.

     

    I have been attempting to alter my count formula to only count the unique [Module] values for Joe but have been unsuccessful.

    I understand the concept of the count formula but am unsure of the syntax to only count each unique instance for a user. Currently I have:

    count(Title[not(Title = preceding-sibling::Title/Module)])

     

    or

    count(xdXDocument:GetDOM("Certificates")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title[not(xdXDocument:GetDOM("Certificates")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title = preceding-sibling::xdXDocument:GetDOM("Certificates")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title div xdXDocument:GetDOM("Certificates")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Module)]) 

     

    I use the count function often along with filtering to achieve my goal but I cant input a custom formula  via the Filter Data option within the "Insert Field or Group" box.

     I use IP and SP 2013 for reference

     

     

     

      

    Nature loving nerd, with a fair amount of commonsense.
  • 03-08-2016 02:30 PM In reply to

    Re: Using COUNT for unique items only

     Maybe this for the unique count?

    count(xdXDocument:GetDOM("Certificates")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:Title != preceding-sibling::d:SharePointListItem_RW/d:Title])

    Hilary Stoupa

  • 03-09-2016 11:18 AM In reply to

    Re: Using COUNT for unique items only

    You are great! I've searched and found your replies from years ago and they have been the answer I needed or put me on the right track every time.

    Nature loving nerd, with a fair amount of commonsense.
  • 03-09-2016 02:07 PM In reply to

    Re: Using COUNT for unique items only

     Quick update! I used your base formula and altered it slightly to achieve my goal. I have to add syntax to exclude blanks in this new field we added :-p

     

    count(SharePointListItem_RW[Number != preceding-sibling::SharePointListItem_RW/Title and Number != ""])

     

     

    Nature loving nerd, with a fair amount of commonsense.
  • 03-09-2016 03:02 PM In reply to

    Re: Using COUNT for unique items only

    Wonderful, glad you have it working!
    Hilary Stoupa

Page 1 of 1 (5 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.