I have an excel sheet that each column presents the data for one quarter. It has 8 sequential quarters, assume q1 to q4 of 2023 and q1 to q4 of 2024. The file has 50,000 rows and can be grouped into 15 different categories. So, I have generated a pivot which shows the average value for each category for every quarter. Now, I want to plot line curves using pivot table as the input to show the trend of change during the 8 quarters. I want the curve to show 2023q1, 2023q2, 2023q3, 2023q4, 2024q1, 2024q2, 2024q3, 2024q4 as the X axis, and the values from the pivot table as the y value.
How can I do it?
1 Like
Hello @Dscientist.lr, welcome to the QuickSight community!
Something like this should be possible with a little more information. If you are able to put some example data in a QuickSight arena view, I can use that as well to help assist you on this.
What are you imagining the output of this looks like? Are you wanting to utilize a line graph or are you thinking about more of a scatterplot?
If you run a function like concat for your x-axis display value, I think we could make this work:
Formatted Date = concat({year}, {quarter})
It is a string value, but it should display properly in ascending order on the visual. Let me know if this helps!
Let me give you some background.
My question is very simple. I am looking for a method to store the pivot table generated by Quicksight and plot its data by a line curve. It is the main question.
I did search over the internet, and could not find the solution. There were comments by people that Quicksight is weak working with pivots as the source of data. So, I generated the pivot inside Excel, rotated it, used it as the data, and plotted it using a Line curve. The line curve I have uploaded is plotted by Quicksight and I want this. BUT, I do not want Excel to do it.
The only solution that came to my mind was to use SQL to replicate what Excel did, but I could not rotate the results of the goup-by in SQL.
Here is the pivot:
I have added required table and plot in Pivot over pivot - #2 by Franc3sc0
Hi @Dscientist.lr, besides using SQL or multiple dataset joins – I’m out of ideas. Sorry, currently this is not possible but it’s on feature request. At AWS, our roadmap is primarily driven by our customers. Your feedback helps us build a better service. I have tagged this as a feature request. More features are being added on a regular basis, so please keep an eye on the What’s New / Blog . You can set up a Watching Alert by clicking on the bell icon.