I’m trying to come up with an expression that counts only unique values in a table field to use in an analytic calculation.
Basically think of having a table where each record is a batch record. We might run 3 or 4 batches in a day. There is a field in the table that records the text format of MM/DD/YYYY (month / day / year) for each batch.
I’m trying to figure out a way in an analytic that will calculate the number of working days.
So what I need is a Count Unique function (or workaround), that Counts the number of unique values for our Start Date field (which is a text field).
I think it would look something like this as an aggregated expression:
on approach that @stefan has suggested would be the following:
Build a generic Table API function which returns the number of records for a specific filter, which is "date column contains "
Set a counter variable to 0. Then, run the connector multiple times which each of the past week’s/month’s/… days as the textfilter. The filter can be dynamic based on the current day using format_date_tz. Increment the counter every time the connector returns more than 0. (Would do that with an if expression so that you don’t need a new trigger every time.)
The resulting number should be the number of working days.
would this work for what you’re attempting to build?? it’s not the simplest, but should work well!!
Doing it this way wouldn’t help me dynamically obtain the number of work days within a table analytic.
I need the number of work days to use as a calculation variable for an operation in my analytics.
I think I just need to wait for the ability to query two tables in analytics. If I can utilize a second table within analytics, I can store the work day values on a separate table using counters within my app and then use that as a calculation variable for my original table.
I spoke with the team requesting this for an app I’m building and I think we’re going to go a different route by creating a manual monthly workday calendar table that will they will have to update and I’ll cross-reference that value for month and year to add as a table field in my batch sheet table.
This way the work days for that month will just be a static field in my batch sheet table. I can then just take the aggregation of the summed average value to use as my calculated variable in my analytics operation which can be grouped by month or year.
Not the ideal way for a low maintenance app, but I can’t see a way around it until we are able to do analytics with multiple tables.
You could add an auxiliary field to your table for the workday counting - the type does not really matter. You just need to be able to store something to be counted by an analysis there. In your app, whenever you create a new entry you check if there is already a row existing which fulfills two filters:
The current MM/DD/YYYY
A value in the auxiliary field
You can use the API for that.
If this is the case, you do nothing. If not, you add whatever you like to the auxiliary field.
That way you should be able to count for how many records in the table the auxiliary field is not blank which should give you the number of working days.
This won’t work for the following reason when using groupings…
Let’s say we have 2 reactors that we use in our operation. On the batch sheet record, we indicate which reactor we use, Reactor 1 or Reactor 2.
Let’s also say that today, we only produced batches on Reactor 1, but not Reactor 2. (Or have a grouping that splits the data between the blanks and auxiliary field values.)
If I’m creating an analytic that uses the Reactor field as a grouping, it won’t have any records that today was a workday for Reactor 2 because the records created today will only belong to the group of records for Reactor 1.
Therefore there will be a bias on calculating working days for each grouping when the values should actually be the same.
I really just need a way to pull in an additional table to use a shared value for both groupings that isn’t dependent on whether each grouping has a record created on that day or whether that grouping has the auxiliary counter included vs. a blank for that day.
I think it would make sense to have a call as well to take a look at the app, the table, and the analytic.
I’m available at most times next week Monday - Thursday. If you can let me know two days / times you have available, I can setup a meeting for us to review. Maybe 45 min or an hour should be fine.
Also if you want to end this thread and just communicate by email, my email is eric.singer@iff.com.
Hi Eric,
I think this is now possible with Table Aggreagtions using the calculation “Unique Values”
Table Aggregations are olso available in triggers to store the result in an array…