Hi, struggling here and, apologies, I can’t quickly create a sample board to replicate as all the data is confidential and there a lot of fields included in the original query.
What we have is a client who has submitted 20 projects. SubmittedAt is just a count of the distinct project submission dates.
CreditsUsedNew is a number associated with a project of how many credits as associated. Nearly all projects in this case were one credit but one was more hence, there are 23 credits here summed against the 20 submissions.
Separate to this are events. Each edit event within a project has a time associated to edit i.e V1 video took 2 hours, V2 took 1 hours etc.
‘Calc- Total Time to edit’ is a ‘sum({Calc - Time to Edit in Hours})’. So, here I’m adding up all the versions and their associated editing time which stacks up fine and, in this case is 36 hours across all the versions within the 20 projects and 23 credits booked. (this ‘Calc - time to edit in hours’ within the calc is just the edit time per version divided by 60 as it’s in minutes initially).
So all I actually want to do here is work out average time to edit each credit which is 36/23=1.56
Whenever, I try and divide one by the other, I get the issue with mixing aggregated and non-aggregated data so I tried this below but whilst the answer is somewhere in ballpark, it’s not accurate i.e 1.74 in this case. (The ‘readableId’ here is the unique ID against each project)
(sumOver({Calc - Time to Edit in Hours}, [readableId], PRE_AGG)/CreditUsedNew)
I’ve mocked up some data (see below) and re-created your calculated fields and table. If I use the following calculated field I am able to generate the correct values for “Calc - Average edit time per project”: sum({Calc - Time to Edit in Hours})/sum(CreditUsedNew)
When you received the “mixing aggregated and non-aggregated data” error it may have been because the aggregation wasn’t being applied to the CreditUserNew field, e.g. the following gives me the same error as it is mixing aggregated with non-aggregated numerators/denominators: sum({Calc - Time to Edit in Hours})/CreditUsedNew
Hi @Turboslippers, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
Also, here is a good article on level-aware calculations. It includes a section on mismatched aggregation errors.
