Percentage - divide one field by another


#1

Hi

Is there any way to divide one field by another to calculate the percentage

Regards
Jayanthan


#2

Hi, not with the query builder. But you can use SQL syntax (if you have a SQL database).
Hope that helps, Eva


#3

Hi Eva

Noted . Thank You

Regards
Jayanthan


#4

Not to contradict you, @EvaS, but that’s actually not entirely true. You can actually create a custom field which divides the values of the rows in one field by another, like this:


#5

Holy cow, i love to be contradicted on that!! :smile:


#6

Hi Maz/Eva

i cannot find the custom field option and field formula, could you please direct me

Thank You

Regards
Jayanthan


#7

Hi, in your query builder you should see 3 dots on the right side, there you schould find sort options and custom field options?
Although I do remember (but can´t find the posting about it) that another user with a non-SQL database was not able to see those fields?


#8

Hi Eva

I cannot find customer field option on the specified place, Please advise

image

Regards
Jayanthan


#9

What type of database is this, Jayanthan?


#10

Hi Maz

We are using mongo database, i think we can create custom fields for sql database

Regards
Jayanthan


#11

Only possible reason I can think of is that the source table is a Saved Question or an SQL Question. I think custom fields is not available for those due to some limitations.


#12

Hi all

Noted,i am using mongo database, customer field cannot be used for mongo, lets close the topic

Regards
Jayanthan


#13

Hi @jayanthan, I’m also on MongoDB, can confirm this is a limitation of the MongoDB driver. Also see https://github.com/metabase/metabase/issues/4447.

I think you can write a native query for Mongo though, perhaps you can do what you need that way?


#14

hi tschut

We should be able work with custom fields irrespective of the database, there is no point in adding multiple fields in the database, rather metabase should be handle calculations

all the working should be done in metabase, thats how most BI tools works

Regards
Jayanthan


#15

Yes, what I mean by ‘native query’ is that you can write one in Metabase :slight_smile: I realize it’s not perfect but it might solve your need right now.

When browsing the issues I didn’t see a feature request for custom expressions in mongo, so maybe you could log it on github? That way people can also vote on it and refine the idea.

Have a look here for an example on native queries in metabase with mongodb: https://github.com/metabase/metabase/issues/2726.


#16

Many BI tools don’t work by doing all the calculations, it’s more efficient if they pass the calculation back to the database to do. For example, if you have A+B, instead of retrieving A and B, then adding them together, it’s more efficient if you just ask the database for A+B.
Makes an even bigger difference if you then filter on A+B.
I know nothing about Mongo, but maybe that’s why there’s the limitation.


#17

Andres

Have you used Qlikview BI where data is downloaded as it is , calculations are done in BI tool. There is no necessary to add fields in the database. Because when we maintain data warehouse, we reduce number of fields in the database and keep the most important datas


#18

Qlikview is an oddity (yes, I am Qlik certified). It’s not just BI, you get ETL and DW all in one UI. It’s really 3 products with one U. It extracts from your source database, then transforms the data for use in it’s own in-memory database.
However, in Qlik, it is still more efficient if you can manage the calculations during the load, though not as part of the SQL.


#19

Doesn’t PowerBI do this too - the concept of the Dataset where you can perform calculations, transform data, and leverage the in memory calculation engine, etc?

My background is in Business Objects, so I’m more used to an abstraction layer to perform these duties.


#20

Don’t forget, in Business Objects, your Universe just passes SQL back to the database. It’s just doing the clever SQL stuff for you.
Sorry, I’m SAP BI certified too!