I have searched and have seen some other similar posts and some Github issues that may be related, but I am not sure. I therefore want to see if maybe I can get some help.
I want to do a very basic thing. I have some data that I want to have in a table. I want to add some calculated columns to that table. I want to be able to sort the data.
As a proof of concept, I made a simple table with data from a view in my database. The data is a bit scattered, so a view made it easy to get it into Metabase.
I made a question from a(n) SQL query. Sample:
SELECT metabase_product_info.status, metabase_product_info.sku, metabase_product_info.product_name, metabase_product_info.brand, CAST(metabase_product_info.stock AS SIGNED),
sum(wp_wc_order_product_lookup.product_qty), sum(wp_wc_order_product_lookup.product_gross_revenue),
metabase_product_info.current_price, metabase_product_info.regular_price, metabase_product_info.cost_price
FROM metabase_product_info
LEFT JOIN wp_wc_order_product_lookup ON metabase_product_info.product_id = CASE WHEN wp_wc_order_product_lookup.variation_id <> 0 THEN wp_wc_order_product_lookup.variation_id ELSE wp_wc_order_product_lookup.product_id END
LEFT JOIN wp_wc_order_stats on wp_wc_order_stats.order_id=wp_wc_order_product_lookup.order_id
WHERE {{date_created}} AND {{brand}} AND {{category_1}} AND {{category_2}} AND {{category_3}} AND wp_wc_order_stats.status in ('wc-completed', 'wc-processing')
GROUP BY wp_wc_order_product_lookup.product_id, wp_wc_order_product_lookup.variation_id, wp_wc_order_product_lookup.date_created;
After creating a dashboard with this data, I have exactly what I want:
It's fast, it can be filtered. I was excited to build out my calculated columns!
But then I tried sorting by quantity sold, and the numbers did not make sense. The highest number was 6 for my filtered view. I did the same sorting using an ORDER BY clause in SQL and the highest number was 20.
Is this not one of the most basic needs of a BI tool? To extract data and to be able to filter and sort that data? The filtering is great, the sorting gives a completely false impression of reality.
It seems that Metabase is truncating the data before sorting it. I have tried converting the question to a model, but that is much slower when querying and I also can't figure out how to sort it.
My only option now is to make one SQL question per sortable column and use tabs to make one report per sortable column. You would need to filter in each tab, and working with the data would be very cumbersome.
Have I missed something? Please tell me if I have missed something and that sorting on the full data set is possible?
Building the query in the "WYSIWYG" editor is not optimal, because then I can't get the nice filters as I understand it? Also, you have to have the columns you want to filter on in the table, if I have understood correctly?