Hi everyone,
I hope somebody can help me. I’m new to AWS Quicksight and trying my first steps. I have a table with two columns. First column contains a unique ID, second column contains a Runtime. What I’m trying to reach is to aggregate the Runtime only once per ID. The Runtime per ID is unique. In the example the correct value would be 38. I’d like to display this in a dashboard as a KPI.
Hi @Markus,
So looking at the calculated fields you’ve tried out, it looks like you’re trying to add up the runtime but only utilizing one row per ID?
You’re on the right track with the min (or alternatively you could also use max). But I think where you might be encountering an issue is using ‘PRE_AGG’ for your sumOver and minOver. If you’re summing Pre-Agg, it’s happening prior to any aggregation.
Have you tried the calc. field without the pre_agg so that it defaults to ‘post_agg_filter’?
Additionally, here’s an old community post that discusses how to deal with duplicates in your dataset, similar to the scenario you are encountering.
Hi Brett,
thank’s for the response.
Removing duplicate rows is not an option, because in my dataset there are some more columns with values in the rows I need for further analyses. Sorry I was not specific enough about that.
I will try your suggestion with not using the pre_agg as soon as I get back to my desk.
Hi @Brett
I tried your suggestion with the post_agg_filter. Unfortunately I get an error and the calculation field won’t be created.
Do you have any other suggestions to solve the problem?
If this does not produce the expected result, please specify the visual in which you are trying to apply this calculation. Ideally, the visual should be aggregated on ID or KPI, displaying some aggregation such as AVG, MIN, or MAX of Runtime.
Hello @Hasnain-VW
thanks for your suggestion. Unfortunately the solution is not correct. I tried
maxOver(sumOver({Runtime}, [{ID}], PRE_AGG), [ID], PRE_AGG) for Runtimeuniquecount
but when I visualize the result in a table, I see the Runtime beeing aggregated over all IDs and not only once. So the result is 148, but the desired result is 38.
Maybe another approach might be helpful… Can someone tell me how to add a column which counts the rows for the IDs. Let’s call it “RowCountID”. This would be my desired result:
ID
Runtime
RowCountID
1
10
1
1
10
2
1
10
3
2
5
1
2
5
2
3
8
1
4
5
1
4
5
2
5
10
1
Based on this column, I could do the further calculations and filtering.
Are you still working on this or were you able to find a solution? It looks like it would be helpful to share more info about your dataset if you’re able to.
