How to apply the right ordering when using Pivot functionality?
I want to order my dishes by total number across all columns, and headers ideally in alphabetic order. But seems Metabase orders then a bit randomly. What is the mistake here?
Hi @Sergej
Without seeing the full query, it's difficult to help. Which version of Metabase?
Testing with H2 Sample Dataset
SELECT year("PUBLIC"."ORDERS"."CREATED_AT") AS "CREATED_AT", "PRODUCTS__via__PRODUCT_ID"."CATEGORY" AS "CATEGORY", sum("PUBLIC"."ORDERS"."TOTAL") AS "sum"
FROM "PUBLIC"."ORDERS"
LEFT JOIN "PUBLIC"."PRODUCTS" "PRODUCTS__via__PRODUCT_ID" ON "PUBLIC"."ORDERS"."PRODUCT_ID" = "PRODUCTS__via__PRODUCT_ID"."ID"
GROUP BY year("PUBLIC"."ORDERS"."CREATED_AT"), "PRODUCTS__via__PRODUCT_ID"."CATEGORY"
ORDER BY sum("PUBLIC"."ORDERS"."TOTAL") DESC, year("PUBLIC"."ORDERS"."CREATED_AT"), "PRODUCTS__via__PRODUCT_ID"."CATEGORY" ASC
Result:
My query is too long, but here is slightly longer version:
SELECT
d.name AS “Diner Name”,
e.name AS “Recipe Name”,
sum(oi.quantity ) AS “Quantity”
FROM view1 as o
........... MANY JOIN statements ...................
WHERE
o.status in (‘n’, ‘c’, ‘cancelled’, ‘in_progress’)
[[and o.organization_name = {{Org_name}}]]
[[and o.site_name IN ({{site}})]]
[[and o.delivery_at >= {{Delivery_from}}]]
[[and o.delivery_at <= {{Delivery_to}}]]
[[and o.delivery_at between {{Delivery from}} and {{Delivery_to}}]]
AND ms.name IS NOT NULL
AND e.name IS NOT NULL
AND d.name IS NOT NULL
GROUP BY “Diner Name”, “Recipe Name”, d.sort_order
ORDER by “Quantity” DESC, d.sort_order ASC, “Recipe Name” ASC
I was wondering if there is any detailed documentation on how Pivot tables work?
I have the latest Metabase version.
@Sergej
Okay, I cannot reproduce your issue on 0.31.2
Try playing around with your ORDER elements. And in the visualization settings, try clicking “Reset to defaults” button, just to make sure to clear anything old.
Detailed documentation? There’s the source code, that’s very detailed.
EDIT: Also, I’m not quite sure what d.sort_order is doing, but you’re both grouping and ordering by it, but it’s not one of the return columns.