Count(Distinct(Collect Help

Stefano Ferretti

Hi All,

I'm trying to count the number of 'distinct' participant to events based on the year and add that count to their org leaders.

In the table where I have the participant I've alredy added helper columns to identify their leaders at different lvl. My formula works fine as long as I point to the specific column where I expect the leader to appear, but it doesn't if instead I reference a group of column … I presume I'm doing something wrong on the 'collect' piece of the information …

here's the formula :

=COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders table}, [Leader ID]@row)))

If the {Event Leaders table} is a single column, then it works like a charm, but if that reference is a group of 4 column, it return an incorrect count

Stefano Ferretti

yeah, no, those columns are already 'helpers' … I ended up with a workaround :

I nested a series of IF statement for each of those column range, adding a reference to a 'level' column which will then point the formula to the correct column in the collect criterion …

=(IF(Level@row = "L8", COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders L8}, [Leader ID]@row))),IF(Level@row = "L7",COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders L7}, [Leader ID]@row))) and so on

it looks ugly as anything, but it get the job done.

Pretty sure I could at this point simplify the whole thing, but my brain is exhausted by this :D :D

Weird that there is no indication of that COLLECT limitation in the function description, it would have made my life a lot easier

Stefano Ferretti

essentially it seems that the COLLECT part of the formula is not working when 1 of the criterion range is not a single column, but 2 or more columns

Adam Murphy

I don't think you can have different sized ranges within a single function. If one range is a single column then you will need to reference a single column for all the other ranges as well, and if one range is 4 columns wide, then all ranges will need to be 4 columns wide.

One easy fix/workaround I can think of would be to add a helper column that JOINS those 4 columns into one cell within each row, then use that column as your range. Hope this helps!

Stefano Ferretti

yeah, no, those columns are already 'helpers' … I ended up with a workaround :

I nested a series of IF statement for each of those column range, adding a reference to a 'level' column which will then point the formula to the correct column in the collect criterion …

=(IF(Level@row = "L8", COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders L8}, [Leader ID]@row))),IF(Level@row = "L7",COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders L7}, [Leader ID]@row))) and so on

it looks ugly as anything, but it get the job done.

Pretty sure I could at this point simplify the whole thing, but my brain is exhausted by this :D :D

Weird that there is no indication of that COLLECT limitation in the function description, it would have made my life a lot easier

Adam Murphy

Ok yeah that's another workaround. I think adding another helper that joins the 4 cells into one would have been easier than that long nested IF statement, but if it works it works! Glad you figured it out. :)