Using pivot table results in Scatterplot

Hi, I was trying to show some X and Y data in a scatterplot. The X and Y is from a pivot table and are partition by some other variables. I managed to get the X and Y data shown in the pivot table, whilst it showed error when I was trying to visualize it in the scatterplot. If I understood correctly, the X and Y data need the corresponding dimensions if we want to show it.

If we couldn’t use the results from pivot table directly, can we do the step in the data preparation step? I have created the calculation field (X and Y data) when preparing the dataset, but it didn’t show the value in the data preview. Only when I visualize it with a pivot table, can I get the values.

The calculation field looks like:
runningSum(sum({v1}),[ v2],[v3])

Are there any methods that we can solve this issue? Any suggestions would be greatly appreciated!

if I understand correctly, you have created a calculated field using “Running Sum”, it shows properly in a pivot table like below screenshot
image

However, you were using the same set of fields and visualize in a Scatter plot but it shows error like below.
image

The error is caused by using a runningSum in Scatter plot. Suppose it is using for showing the running total. Can share your use case why you want to show a runningSum in scatter plot? Thanks

Hi, thanks for replying to me!

Your interpretation is correct, it was just me that didn’t make myself clear.

So we need to get the “Running Sum” because we need it to be divided by another variable and get the values. Imagine that we have running sum X which needs to be divided by A, and running sum Y divided by B. Finally, we need to show the both results in the scatter plot, and the data has different dimensions.

The reason why we need to do this in such a tedious way is that we have data in 2 different databases (from PostgreSQL) and the calculations could only be done after we join the tables on Quicksight.

Thanks!

thanks @emma_chan, could you mind giving some hints or share an example about the business use case such as what kind of data you want to present? for instance, sales amount.
As running sum is not supported in scatter plot, would like to understand the use case and see how we can sort out

1 Like

Hi, @emma_chan.

Can you move the desired calculation (running sum) to the database layer (using SQL if possible) and then use the field in a scatterplot?

We hope (my) solution works for you. Let us know if this is resolved. And if it is, please help the community by marking his answer as a “Solution.”