How do I convert some column headers into number and use it as X-axis in a line chart?

Question & Answer
,
1

Hi QuickSight community,

We are very interested in QuickSight and started investigating it. I have a data sheet containing the performance data looks like

test case, year, 1 loc (ms), 5 locs (ms), 10 locs (ms), 50 locs (ms), 200 locs (ms)
test1, 2023, 500, 1000, 2000, 5000, 7500

I would like to draw a line chart with x-axis the number of locations and y-axis for the performance time. Do you have any idea how to achieve that? Very appreciate it!

Best regards,
Bill

2

Hi @billzhang3046,

Is the year relevant in the visual you’re trying to create? If your dataset includes many test cases, are you trying to aggregate the numbers?

2 Likes
3

Thanks @David_Wong for reply.

We have a couple of dozens of test cases. The year is part of visualization but used to compare the performance. For example,

test case, year, 1 loc (ms), 5 locs (ms), 10 locs (ms), 50 locs (ms), 200 locs (ms)
test1, 2023, 500, 1000, 2000, 5000, 7500
test1, 2022, 700, 1100, 2200, 5500, 8000

We are looking for either a line/xy scatter chart with two line series (2022 and 2023) to compare the the performance trend on increasing locations or a bar chart with category x-axis to compare the different performance between 2022 and 2023 in a more direct way.

We do not aggregate the numbers cross the rows at this moment. Each test case is independent.

4

Hi @billzhang3046,

You need to unpivot your data to make it look like this first:
image

You can then create a line chart comparing 2022 and 2023:

image
image1133×697 31.8 KB

If you have multiple test cases, you can use small multiples.

image
image1131×673 32.3 KB

1 Like
5

Thanks @David_Wong! This was I thought and I can successfully create a chart by pivoting the columns into rows. However I found one issue with line chart which marked the x-axis as category not values. Shown above in your reply, the x-axis is evenly divided between 1 and 5 and 50 and 100. I used to use a scatter with smooth line in Excel to make the x-axis as numbers and show the correct trend by increasing locations. Do you have an idea how to do it in QuickSight? I only see Scatter Plot and could not find where to add line to them.

6

Hi @billzhang3046,

Would you be able to share a screenshot from Excel? I’m trying to better understand what you’re trying to replicate.

7

Hi @David_Wong Below is what I am expecting.

image

You can see the trend is more accurate and should look like linear. With category type x-axis the line cannot reflect the trend correctly.

8

I see what you mean. Unfortunately there’s no way to add a line to a scatter plot in QuickSight.

9

Hope the feature can come soon. Thanks again @David_Wong!

1 Like
10

@Kristin
Can you please tag this as a feature request?

11

Hi David and @billzhang3046. I have tagged this as a feature-request.

Thanks for reaching out! :slight_smile:

1 Like
12

Thanks Kristin. Is there any update on this thread?

1 Like
13

Thanks for checking in @billzhang3046. AWS, including Amazon QuickSight, has large roadmaps, and QuickSight has a policy not to release or share those roadmaps in detail.

That said, QuickSight regularly releases updates and new features. We recommend keeping an eye on the What’s New section of the community to see new updates/features: What's New / Blog - Amazon QuickSight Community

Thanks again for touching in.

1 Like