@neelay Thanks for your question. If you don’t get a reply soon from one of our community members, we’ll reach out to our internal experts on Tuesday to see if we can get a reply for you.
I think you need a different calculated field for your donut chart. If your first calculated field is called sumover, then the new one should be:
sumOver({sumover}, [{rule_name}], PRE_AGG)
If you’re not getting this result, I think it means you have some data in your dataset which is not being displayed in your pivot table. Do you have more fields in your dataset that aren’t in your pivot table? Can you select your pivot table and show a screenshot of the field wells?
@David_Wong thanks for trying to help me… really appreciate the effort.
My issue is the aggregation of value which was derived with sumOver() formula.
I might also be misunderstanding your question. Is your sumover field the same as your custom_total field? The screenshot of your donut chart shows the sumover field in your field well but you also wrote custom_total.
Thanks @David_Wong for fielding these questions. @neelay Thanks for reaching out to the QuickSight Community. We’d love to help you find a solution. Let us know your thoughts on David’s question above.
BTW, all 4 pie charts are wrong, though 1st is showing the expected result, the other 3 are wrong as well as showing the unexpected result.
We already went through sum(sumover()) example… So I didn’t include them in the screenshot.
You said that the 1st pie chart is showing the expected result. In what way is it wrong?
Do you not want your donut chart to show the total for each rule? Are these the totals that you’re expecting?
ICD-10 code: 1078
ICD-9 code: 3
FDA Code: 1212
@David_Wong Because of the shared_with column, I have duplicate rows (they are not true duplicates),
The numbers appeared “correct” in the 1st pie chart but they are not, ICD-10 is off by 5
If I hide the shared_with column, the total will sum the duplicate values, and that’s why I using SUMOVER. only custom_total shows the correct value…
I am asking why sum(custom_total) is not working.
if sumover is not intended to work this way, I am also thinking what’ is the use case of sumover then?
OK, I understand your problem now. You need to calculate your total like this:
There are 2 nested aggregations here. The inner aggregation says that for every combination of rule_name, event_date and channel, take the min of total. This effectively removes your duplicates. The outer aggregation then takes of sum of the mins to give you a total of 1073 for ICD-10.
it didn’t work for me either… min(custom_total_3) where custom_total_3 = sumOver(total,[{rule_name},{event_date},{channel_id}],PRE_AGG)
still show 1 in pie chart.