Vertical Faceted chart not sorting correctly

leaflove
leaflove Member
edited January 23 in Magic ETL

I'd like to create a faceted chart that shows revenue for a subset of products, comparing multiple months against each other. This would give me a visual representation of changes month over month for my top products, and would promote further investigation if a product's performance one month was significantly different than previous months. It might tell me that a product is/was out of stock, or a variety of other possibilities that I could then address.

I'd like to sort the series in reverse chronological order top to bottom by month-year. So the current month would be on top, followed by the previous month, and so on. Why isn't my chart sorting correctly? Notice how the previous two months are out of order?

image.png

image.png

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    It looks like you are limiting the data to 150 rows. When you move the Year | Month to your first sort, then it moves the most recent month to the top of the data, and you only get the most recent month. You should remove that limit to see all months.

    If you're limiting to 150 rows in order to get a more limited number of products, you should create a formula that ranks products based on the relevant metric, and then filter on that formula. Or, you could add a filter card that allows users to filter by the SUM of Net… value you have in your X axis now, so they can tune the appropriate range to look at products of interest.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • GrantSmith
    GrantSmith Coach

    You're sorting by the SUM first and then sorting by the year. Move the Year to be the first thing you sort on.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • leaflove
    leaflove Member

    @GrantSmith When I change the sort order, it eliminates the other months for some reason.

    image.png

  • leaflove
    leaflove Member

    @GrantSmith By the way, I changed it from Vertical to Horizontal. Not sure if that makes an impact.

  • leaflove
    leaflove Member

    @GrantSmith What's also interesting, is that it looks right in this image. But when I filter, it changes the month order. So it must be something to do with the Revenue sort, but I'm not sure how the chart works.

    image.png

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    It looks like you are limiting the data to 150 rows. When you move the Year | Month to your first sort, then it moves the most recent month to the top of the data, and you only get the most recent month. You should remove that limit to see all months.

    If you're limiting to 150 rows in order to get a more limited number of products, you should create a formula that ranks products based on the relevant metric, and then filter on that formula. Or, you could add a filter card that allows users to filter by the SUM of Net… value you have in your X axis now, so they can tune the appropriate range to look at products of interest.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • leaflove
    leaflove Member

    Thanks @DavidChurchman, that worked! Now I just need to figure out the filtering mechanism.

Categories