1

Motion ranking

Hello all, very new to Pyramid (day2) and I've got a bar chart with date driving motion and am trying to sort the display order of the bars to always be descending (so they may have to swap places on x-axis) .

I've got a field in the data that gives a rank for each item for each date and i'm using that as a sorting value but this isn't working, it appears to be using the total rank value for each item over the time period as the value to rank from.  Am i chasing something that cannot be done?  many thanks, Adam

 

(apologies if this post happens twice, first one doesn't seem to be showing)

7 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 4 yrs ago
    • Reported - view

    Hi Adam,

    Your post needs to be approved by the moderators of the forum before it appears in the Q&A, so that's why your post does not appear immediately. I'll answer your question in reply to this post, so you can delete the previous one.

    Okay, this is an interesting question and tbh, I've not attempted it before but my curiosity was piqued as to how it could be done. There are a number of things to think about with regard to what you want to achieve. The first is that you are wanting to sort your items within an outer dimension. I'll use our sample demo DB to illustrate.

    If I create a grid with months and manufacturers in the rows and sales as the measure, I want to sort the manufacturers within each value of months. This is a nested sort, or in query parlance "N of N". i.e. show me the top manufacturers in the top months. Except in this instance we don't want to sort/filter the outer dimension (months), we want their natural order.

    Pyramid supplies a query option called N-of-N which will provide the mechanism to achieve this, then we'll need to tweak the visualisation to give us what we want.

    First, create a grid containing the data we want:

    Now we need to apply the N-of-N sorting. Click on the Query sub-menu and you'll see the N-of-N button around the middle of the ribbon. Click it then apply the sort criteria.

    For the inside sort, I'm using the Top Count filter. Top count will sort and then filter the top n items. As I want all my items, I use a Top N value equal to or greater than the number of items  in the list. In this case there are 11 manufacturers so I choose a number greater than 11. I also choose the measure I am sorting against.

    For the outer sort, we need to be a bit sneaky. I want my manufacturers sorted inside each month, but I don't want the month sorted, I want to retain the natural order. I also don't want to filter out any month accidentally. Choose the Equals filter and set the value to be equal to to be something that will never occur, in this case Sales Equals -1. then choose to DESELECT rather than SELECT the items based on the filter. This will retain in my months list all months where sales do not equal -1 and retain the natural order of my months. Hit the apply button to apply the N-of-N filter.

    You should have something like the above now, with Months  in the correct order and your items sorted within each month.

    Now we need to convert the grid to the chart you need.

    Click the Column chart button.

    Move the Months Chip from the Colour drop zone to Motion and the Manufacturers (your items) from the Vertical Trellis drop zone to Colour.

    Hey Presto! You should have something like this. I've added category labels so it's easier to follow the moving items. You can of course, edit the colours assigned to each items by right clicking on the item in the legend and choosing "Edit".

    Press Play and enjoy!

    I've attached a video of my charts in motion.

    Hope that helps.

    Ian

    • Adam_Taylor
    • 4 yrs ago
    • Reported - view

    Ian this looks amazing!! i did wonder about that N of N function but as a new starter its all too far out right now. I'll have a go at this today its very exciting and i'm generally loving the animated nature of Pyramid charts which really help users to see what changed when they interact with the dashboard.  Thanks again

    • Adam_Taylor
    • 4 yrs ago
    • Reported - view

    Ok i got the table looking just right which is ace but the chart was still showing items sorted by a single overall value. Lots of connection issues today making work very difficult so i will try again in morning early hopefully everyone still in bed and network is smooth! will feed back thanks again

    • Senior Director of Product
    • Ian_Macdonald
    • 4 yrs ago
    • Reported - view

    Make sure the Category drop zone is empty. Items (Manufacturers in my case) chip in Colour and Months in Motion for the Column chart.

    • Adam_Taylor
    • 4 yrs ago
    • Reported - view

    Hi again Ian, ok a better connection made this much easier today, and it works just as described many thanks! i suspect the NofN item order counts? back to my learning thank you.

    • Adam_Taylor
    • 4 yrs ago
    • Reported - view

    Hi Ian, so the next question please, is why do i need the Geography item (manufacturers) on colour? i can add additional measures to colour but cant remove the manufacturers. Ideally i'd like to learn more about how this works by displaying the bars all the same colour please. is it something to do with the trellis?

    • Senior Director of Product
    • Ian_Macdonald
    • 4 yrs ago
    • Reported - view

    In this scenario, we are wanting to keep the query as generated by creating the grid, with Manufacturers sorted within each month. By placing Manufacturers into the colour drop zone, we are not impacting the query results, just colouring them based on the Manufacturer value. If you move Manufacturers to Categories, you will change the query result and lose the ordering as you have discovered.

    the colours used depend on your Theme. you can create a theme that uses the same colour for all items, or easier, is to create a new Measure set to  a value (like 1) and use that to drive the colour so that all the bars are the same colour. In fact, if you use the "Custom colour" option in the colour drop zone, the value will be used as an RGB number to drive the colour, so can select from the millions available by setting the Measure to whatever RGB value you want.

Content aside

  • Status Answered
  • 1 Likes
  • 4 yrs agoLast active
  • 7Replies
  • 47Views
  • 2 Following