I have a data table in Spotfire which has a list of items belonging to different management groups. Each management group gets new items every day. I want to calculate the daily average number of items for each management group over a filtered range of dates. Not every management group gets new items every day.
I created a pie chart that is colored by [management group].
After I filtered the range of dates to 9/25/2023 - 9/27/2023, I tried to size each sector with the expression:
count([item]) / uniqueCount([date])
I expected this to divide the total number of items in each management group by the number of dates in my range, 3.
This worked for most of the management groups, which have items for each day, but one management group, "K", only has 1 item for 9/27/2023, and no other items on the other dates, so the uniqueCount of dates for that management group is 1, thus the displayed average for management group "K" is 1.
Management Group "K" is in yellow
I want the pie chart to show an average value of 0.33 for management group "K" (one item in three days between 9/25/2023 - 9/27/2023).
I can't just divide the data by 3 because it must depend on the filtered dates, so the pie chart would show a value of 0.25 when the dates are filtered to 9/24/2023 - 9/27/2023 (one item in four days), or 0.5 when filtered to 9/26/2023 - 9/27/2023 (one item in two days)
Could you try substituting the denominator with the maximum count of dates in the dataset? Something like
count([item]) / Max(uniqueCount([date]))