Multiple Measures by Seperate date fields in same pivot table

I'm coming to metabase from a different BI software and struggling with how to generally approach this topic. Apologies if this is commonly answered but I'm not finding any results using the key words I am use to. Would appreciate any links to the relevant documentation or even just correcting my terminology so I can search better.

Here is what I'm trying to achieve. I have an SQL query listing key details for rental orders. I would like to summarize this in a pivot table grouped by salesperson have columns for key measures such as 'Orders Created This Month', 'Orders Shipping this Month', 'Orders Returning this Month', 'Orders Out'.

Each of these columns require an aggregation of the SQL Query on different date columns.

Is this possible in one table? Or does each measure require it's own Question and then be put next to each other on a dashboard? If they are separate Questions then surely I'll never be able to line up by salesperson?

I could do this all aggregation in the SQL but won't that then mean it lacks any drill down ability?

Sorry if that's a bit rambly - how would you approach making this in Metabase?

Kind Regards

@SMBeresford Your options depend, in part, on how your data is organize. You could create separate Questions for each measure and then join them all together to create a data source. This article isn't specific to the problem you're addressing but it does give a good overview of methods you can use to to join multiple questions that have date involvement.

I'm not sure what articles you've already found but the Learn and Docs content is vast so I picked a few specific things that I think might be valuable to you:

You're correct that writing the underlying question in SQL would impact the availability of automatic drill downs. However, you can still create custom click behavior on the dashboard cards if that's something you'd be interested in exploring. You can also explore converting your queries into Models and manually adding Metadata to bring back drill through functionality.

I hope this is helpful but if you would like to provide a little more information about the shape and structure of your data I may be able top provide more targeted advice.

All the best,

Thank you @leeloo that's given me some direction and confidence. I'll keep giving it a go.

1 Like