I am trying to create a table from the exisiting question where all my data are being ordered by quantity and date Desc order. In my systems no orders can be placed in the past, so the new orders will always have a date in the future.
When I try to create a two-dimensional table, I don’t know how can I sort the table by total sum in the row, and month in columns. I would like to see:
Delivery Timeslot | 19-Apr | 19 Mar | 19-Feb …
but I see
Delivery Timeslot | 19-Feb | 19 Mar | 19-Jan … 19-Apr
Here is how my report is being defined:
I would not want to show users 2019/04/01 as 19-Apr is much more user friendly, also I would like to avoid creating a custom SQL just to make such sorting.
If you want to sort by “Delivery Month”, then use that as your sorting option instead of “Sum”.
But “Delivery Month” has to be a
date type. If it’s just a string like
varchar, then it cannot make magic and you would have to use another column for sorting.
Here’s an example with Sample Dataset:
I want to sort columns by Month and rows by Total sum hence I need to use Sum in the Sort field. If I use just month then my values won’t be sorted from largest to smallest value.
But since you don’t have multiple “Delivery Month” with the same value, then the “Sum” would never have any effect.
Otherwise just add multiple columns to sort by.
I have checked your approach on the Sample database and it seems what I want does not work.
On the screenshot below, it says “Enormous Marble Wallet” is the top item with total of 16 across 5 months, but “Sleek Leather Toucan” has 24 rows in the same time period. So the Metabase sorts the data based on the value for the latest month rather than total.
Okay, now I understand. But that would require a sub-total column to sort by.
There’s a request for more advanced pivot features - upvote by clicking on the first post: