Distinct Counts with dates and multiple criteria

Tshelton

Hi all,

I am hoping someone can help me out with my test tracking grid. I am trying to create multiple formulas for our tracking reports and seem to be falling short.

Above is a screen shot of our testing page. I need to get a count of the following:

• Count of test ID's that had at least one test completed - For this example the answer should come back as 7 because we only want to count Test ID 1606 once.
• 
  
• Count of Distinct Test ID's with a result over 8 at the most recent schedule date - For this example the answer should come back as 1 because only Test ID 1624 had a result over 8 at their most recent schedule date. If the Test ID had a result over 8 at an earlier date I don't want to count it.

I don't know if I need to create helper columns or change the tracking form to make the Test ID the parent and then list each schedule date as a child row or what, but I can't seem to get the formulas to work. I am admittedly not great with more complex formulas - still learning. Any assistance is greatly appreciated!

Dale Murphy

@Tshelton

=count(distinct([test id]:[test id])) should get you the first one ...

I will take a run at the second one later ... a little trickier.

dm

Tshelton

Thank you! That second one is proving to be a pain. I might need to change the way we report that - or maybe add some sort of helper column somehow. Thanks for your help on this!