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

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

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

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.

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:

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

1 Like

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.

Hi @billzhang3046,

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

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.

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

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

1 Like

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

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

Thanks for reaching out! :slight_smile: