Controls

Re: Using COUNT for unique items only

Hilary Stoupa — Wed, 09 Mar 2016 23:02:56 GMT

Wonderful, glad you have it working!

Re: Using COUNT for unique items only

GEE OTTO — Wed, 09 Mar 2016 22:07:01 GMT

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 != ""])

Re: Using COUNT for unique items only

GEE OTTO — Wed, 09 Mar 2016 19:18:00 GMT

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.

Re: Using COUNT for unique items only

Hilary Stoupa — Tue, 08 Mar 2016 22:30:58 GMT

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])

Using COUNT for unique items only

GEE OTTO — Tue, 08 Mar 2016 21:29:31 GMT

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 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)])

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