I am having trouble figuring out how to use a total from a column in a Table analysis I’ve made.
I want to use this total as the numerator in another KPI visual.
The table with the total I want to use is an aggregate of visits per member, with multiple control filters (using parameters).
Since the column is dynamic, based on the control filters, my total will be dynamic as well.
I’ve tried to replicate this column in a calculated field (analysis) using the parameters in the code (so it filters it with the controls), so that I can use this calculated field as the numerator in my KPI.
However, it won’t allow me to create the calculated field bc it states you cannot use aggregate values in a calculated field. (The table rows are the sum of visits per member)
I hope I’ve explained that with enough clarity… any insight into how else I may be able to capture this dynamic column total in another visual or calculation?
Originally I thought it may be bc I’m trying to use the calculated field (column total) in a KPI, which only takes one field, but it won’t even let me create the calculated field before I create the visual. So pulling in all of the fields into my visual shouldn’t be the issue (yet).
This is the error from the calculated field that should recreate the table column total value. Sorry, it’s long bc I have a whole bunch of filters/parameters. The error is pulling in the values from the filters/controls currently set. My code actually contains the names of the parameters :
“Nesting of aggregate functions like DISTINCT_COUNT and COUNT(DISTINCT CASE WHEN SUMOVER(SUM(“edtr_visit”), “member_identifier”) >= 0 AND SUMOVER(SUM(“edtr_visit”), “member_identifier”) <= 100 AND SUMOVER(SUM(“urgcare_visit”), “member_identifier”) >= 0 AND SUMOVER(SUM(“urgcare_visit”), “member_identifier”) <= 100 AND SUMOVER(SUM(“office_visit”), “member_identifier”) <= 100 AND SUMOVER(SUM(“office_visit”), “member_identifier”) >= 0 THEN “member_identifier” ELSE NULL END) is not allowed.”
Hi @cmzim. Checking in. We have not heard back from you regarding your question. We’d still like to help. If we do not hear back in the next 7 days, we will archive the question.
Hi @Max. The actual calculated field with the parameters is below.
Thank you for the help. This is really convoluted and difficult to explain, but I will try. I am totally open to suggestions, I just thought creating a calculated field would be easiest. My goal is to capture the dynamic total distinct members (I circle in red), so that I can use it as a numerator in another KPI visual (to show as a percentage of the total population of members).
My full dataset is a list of members with a row per visit. The table rolls up total visits per member for each category of visit. The controls filters the total number of visits per member for that category.
I didn’t use the table “sum” option per member for my total columns, because I couldn’t get the filters to work on a sum “group by” method. So I created a calculated field for each column that does a group by sum (example “Total_EDTR_Visits”) and filter on the calculated fields. These total calculated field columns can be seen in the formula below (Total_EDTR_Visits). The filters in the formula are the controls I have set up to filter these calculated total columns (example ${MinEDTR} ).
I’m just trying to replicate the “100” that I’m getting in my table, so I can use it elsewhere. It seems my issue is that my total columns in my table are a sum in themselves and the calculated field formula below doesn’t want to “nest” aggregate values?
I hope this helps explain.
Thank you!
Trying to upload a sample picture of my table with the controls.
Hi @cmzim,
Thanks for all the additional information. As also stated by you, this question may be difficult to understand in detail through the community forum. I will message you privately to discuss possible solutions.
