I am new to Metabase and trying to resolve something that should be fairly easy.
I would like to sum two different fields (Promotion Value and CC Order Item Options --> Original Unit Price) that I obtained in the same table through a Left Join (I couldn't put a second image, but happy to if that's helpful in a second reply) on the ID of the CC Order Items table to the CC Order Item Options table Foreign Key.
When I try that through a simple sum calculation it seems like it screws up the fields themselves and I don't get the right result.
Hi @ff9991
Please post “Diagnostic Info” from Admin > Troubleshooting.
You should be able to post more screenshots now - otherwise just create a comment with the extra screenshot(s).
I’m not sure I understand what you mean by it screwing up the fields and the results.
Basically, it seems like the custom field does not sum properly the two fields I am considering and also changes the values for those two fields I am trying to calculate on when I insert it in the visualization / table.
@ff9991 I still don’t understand. You’re not summarizing anywhere. Can you show a screenshot, when you have tried to do everything?
By the way, latest release is 0.38.0.1
If you aggregated by a date column, then use that instead of “CC Order Items Options -> ID”.
But you are summarizing without using the Custom Column, so that will never be included, not sure if you even need it.
Sorry, let me try again with explaining and showing what I would like to achieve, as I have been trying to understand the way the tables were thought through.
Basically, I would like to obtain a sum of 2.8 + 0.3 + 0.55 for February 19 (and same logic applied for each day of course), while, given the way the tables are structured (i.e. every Order Item like Starbucks Chocolate might have 2 or more 'cc Order Item Options Names', so I needed to join them together), I obtain a duplicate of the 2.8 value when trying to sum, because I have 2 different Item Option Names (highlighted) that duplicate the '2.8' value, which is referred to the 'Name' column and this can happen for every single Order Item Name.
Let me know if this is clearer now and sorry for the confusion above.
Also, thanks for sharing the documents as I will be reading through them.
@ff9991 Not sure I completely understand. But if you have duplicate rows, then you need for filter them away before doing all the other stuff you’re doing.
I don’t have your data - if you can provide an example using the Sample Dataset, then it’s much easier to understand and reproduce.
Yes, my problem is how I would filter them as they are a result of my join basically. So I was wondering if there is a logic I could use to make it work.
Please find here the [spreadsheet] in which I have put the couple of calculations to arrive to the final result I want to achieve. I have highlighted in yellow the cells with the values I would like to get.
Please let me know if you have a hint based on this and thanks!
@ff9991 You would first have to make a query, so you have something to work from. Your data is in a format, where no tool would be able to do what you want directly.
It’s not really related to Metabase. You wouldn’t be able to fix your data in Notebook, you’ll need to do SQL to do that.
First you’ll need to add the options summed, and then you’ll need to only return unique rows, and join in the options summed from before. Then you’ll have a question that you would be able to use in GUI as a Saved Question starting point.
