Problem: I need to limit duplication of data using a calculation & keep getting errors.
Error: Mismatched aggregation. Custom aggregations can’t contain both aggregate “MAX” and non-aggregated fields “MAX(“questions_asked”)”, in any combination.
The visuals are a table and pivot table. Both produce the same result.
Example:
Data being reviewed is answers from a survey. These answers are Yes, No, N/A only. Each Country has the same survey and the same 3 questions:
Completed Survey
How Data Looks Imported
USA answered 1 question No. The survey data will appear in the questions_asked with 9 questions instead of 3.
UK answered 2 questions No. The survey data will appear in the questions_asked with 12 questions instead of 3.
{audit_status} = string = status of survey
{questions_asked} = # = total number of survey questions asked
{location_name} = string = location of survey
Calculation 1:
used to remove the duplicated data
max({questions_asked}, [location_name]) - returns a valid and accurate representation of total questions_asked in survey.
Calculation 2:
should be to show all completed surveys & the correct total # of questions asked.
ifelse({audit_status} = ‘Completed’, {questions_asked}, null) - returns an inaccurate number of questions_asked completed status’s and needs to be grouped at the location_name (unique id).
OR
maxOver(ifelse({audit} = ‘Completed’, {questions_asked}, null), [{location_name}], PRE_AGG) returns an inaccurate number of questions_asked completed status’s and needs to be grouped at the location_name (unique id).
Thanks,
Side Note: Query has many joins and is connected to a live data source. It is not optimal to adjust the query.
Can you show the visual that you’re trying to create so we can work backwards to figure out how to get there? Are you trying to create a table like this?
The table was to provide a visual for you of how the data is being pulled and duplicated. I need the calculation as specified above to produce DPHO, defects per hundred opportunities. Due to the duplication of data the number is incorrect and I need to be able to group by location_name as this is the only field that is unique.
How does the number of defects or opportunities relate to the survey or questions? How do you want to show DPHO? Do you want to show this number per location in a table or in a KPI?
Take a look at the solution to the question below and see if it helps. It also involved duplicates.
We need to calculate Defect Per Hundred Opportunities of action items when an audit is completed.
Problem: The query pulls in duplicate data due to need of the report and need is to blend data within the query.
Note: Using count distinct or level aware for location_name will provide provide accurate counts for any field.
Total Questions Asked Calculation: max({questions_asked}, [{location_name}]); produces correct totals
Completed Audits Calculation: ifelse({audit_status} = ‘Completed’, {questions_asked}, null); produces incorrect totals due to duplicate data. When attempting ifelse({audit_status} = ‘Completed’, {Total Questions Asked Calculation}, null) an error is recevied “Error: Mismatched aggregation. Custom aggregations can’t contain both aggregate “MAX” and non-aggregated fields “MAX(“questions_asked”)”, in any combination.”
This calculation didn’t produce correct results: maxOver(ifelse({audit_status} = ‘Completed’, {questions_asked}, null), [{location_name}], PRE_AGG) it provided the same results as ifelse({audit_status} = ‘Completed’, {questions_asked}, null)
Final Calculation Needed: count(questions_asked) / total audit questions asked of a completed audit * 100
