Hello,
I’m trying to build a line chart showing for a few locations headcount growth for a few months (x-axis), with headcount values on y-axis, so something pretty basic.
The issue is that in my imported table, each month has its own column filled with values, so I don’t have a Month dimension which I could just import into the dashboard.
Typically in Excel I would create a pivot showing the total values for each month by location, and create a graph on top of it.
What would be the best way to build this in Quicksight?
My table and columns in QS looks like this (made up values):
Location city - May - June - July - August - Total
City 1-----------1--------------3----------------4
City 2--------------------1-------------5--------6
City 3--------------------4-----2-------1-------7
etc.
And this is how I would like the QS dashboard to present the data (this one was done in Excel from the Pivot):
I have tried to use calculated fields for the Month (sum), but I can’t put those into the dimension (x-axis)…
Hi Naveed, thanks for the reply!
The thing is I don’t have a single date column. I do have 4 different columns for each month (May, June, July, August), and each one of them with numerical values.
So I’ve got to leave those columns as Integers cause those values would need to be calculated together for each city. That’s why I was thinking perhaps to use a calculated field, but I just started with QS and not sure how to best use this to obtain the historical chart.
The other solution would be to re-create another table with one column Month, but it’ll take too much work each time I need to import an update with a new month…
I think this is more of an ETL issue than a QuickSight one. What kind of datasource are you referencing? Database, local file, etc.?
If you are referencing a db via SQL, you should be able to pivot the data in your query.
If you are loading a local file, consider how you might shape it (and from the sound of it, subsequent files will follow the same format) to be better suit your needs, prior to loading into a data visualization solution.
Yes this is a local file that I am uploading into QS.
I’ve been thinking of updating the source to a better format so the visualization would work, but since I’m going to receive an updated file from an external dependency on a monthly basis, I was wondering if there would be a way to achieve this in QS, before having to reformat the source material…
