Creating 3-level dataset from another dataset error: Something went wrong

Hello guys,
I am trying to create a dataset using another dataset but I am getting the error: Something went wrong.

I need to create a 3-level dataset: dataset1 → dataset2 → dataset3.

The cause of the error is a calculated field that contains a division formula, I already tried everything but still, I can’t create dataset3 if dataset1 or dataset2 contains the calculated field with a division.

For example, if I create a really simple formula: sum(1)/sum(1) (in dataset1 or dataset2) it breaks dataset3.

Any ideas how can I solve it?

Hello @miguelhentoux, would you be able to add the calculated field after linking all of your datasets together, or possibly adding the calculation into the custom SQL when you query that dataset? It is hard to know exactly why that would be breaking, but I’d imagine the above options would be likely to fix it.

Alternatively, you could also consider trying a different join for the last dataset. Maybe a full outer join would help avoid breaking the last dataset.

Let me know what you find out after trying some of these suggestions, and if you can’t get it to work, we can dig a little deeper. It may also be worth considering building the joins in a single custom SQL statement rather than joining the datasets in QuickSight.

  • I need the calculation field in all three datasets, so I can’t add it just at the end.

  • I didn’t try to replicate the calculation in a custom SQL, probably I can do it but it is so frustrating that I need to create a workaround just to make a division formula, and every time I want to do it I need to go out of QS and move the calculations to the SQL.

  • The last option will not solve the problem, the joins are not the problem.

Were you be able to replicate the error?

Hello @miguelhentoux, I have not seen this same error on my end. Are you using any custom SQL in QuickSight or are you creating datasets directly from views/tables in your datasource? You can write custom SQL queries in QuickSight that would not require you to make those updates externally and you could left join the other 2 tables that way. Then you would only need to query your datasource once, rather than 3 times before joining them.

Hello @miguelhentoux, do you have any further questions on this topic? I will mark my last response as the solution, but if you still need assistance, please let me know!

Sorry Dylan for the late response, I solved my problem by removing the calculations from QS and moving it to a custom SQL. But I would say this workaround is frustrating because it took me a lot of time to realize what was going on and in the end, I had to delete all the formulas with division.

I believe it is a bug in QS and the develop team should take a look.

if you want to replicate it, you can use a simple CSV data:

For example, this is Example:
Formula: test_sum = sum(1)/sum(1)

From this dataset (Example) I created another one (example-layer-2):

In the end, creating the 3rd level dataset (from example-layer-2) it brokes:

If I remove the field test_sum in example-layer-2, the 3rd level dataset works:

1 Like

Hello @miguelhentoux, first of all, I am glad you were able to fix the issue you were facing. I also understand that rebuilding this with custom SQL can be time consuming and I completely understand the frustration. Due to that, I will tag this topic as a bug and archive it for the support team. Thank you for your feedback!