Custom Column (Sum) with 2 integer columns

[ff9991]

Hi there,

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.

Please let me know if you have any idea on how I could solve this and thanks!

[flamber]

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.

[ff9991]

Hi flamber,

Thanks for that.

Please find attached the other screenshot:

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.

Please find below the “Diagnostic info”:

{
“browser-info”: {
“language”: “en-GB”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.7+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.7”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.7+10”,
“os.name”: “Linux”,
“os.version”: “4.14.198-152.320.amzn2.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“mongo”,
“postgres”,
“googleanalytics”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “12.3”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-10-26”,
“tag”: “v0.37.0.2”,
“branch”: “release-x.37.x”,
“hash”: “ba7be09”
},
“settings”: {
“report-timezone”: “Europe/London”
}
}
}

Thanks!

[flamber]

@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

[ff9991]

These are the calculations I used to get to the results I am also outlining just below:

The problem is I would like to get those numbers aggregated by date and giving me a final result of 5.95.

Can you also let me know how I can update the release please?

Let me know if you have any hint or I should share more info and thanks a lot!

[flamber]

@ff9991

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.

You might want to read the documentation about the Notebook editor:
https://www.metabase.com/docs/latest/users-guide/custom-questions.html

And you’ll probably find our learn articles informative: https://www.metabase.com/learn/

You can read more about 0.38 and there’s links to documentation on how to upgrade:
https://www.metabase.com/blog/Metabase-0.38/index.html

[ff9991]

Hi,

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.

I have prepared a better and cleaner view below:

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.

Thanks!

[flamber]

@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.

[ff9991]

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!

[flamber]

@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.

[ff9991]

Ok, thanks for clarifying that.

I was just a bit unfamiliar with the way to do that through Metabase, but I think I managed to find the solution in the end, thank you for the hints!