Often times when a dataset is added to Domo, it won’t meet the need of every card that we might create the future. We might need to create new fields based off existing data, or we might need to calculate the difference between two dates or between two numbers. Fortunately, Domo provides a very flexible way for us to perform these functions and allows us to do this within a Magic ETL transformation or in the Analyzer when creating a card.
If you are needing to perform these calculations within Magic ETL, drag the Add Formula tile in the Utility section into your ETL and connect to the previous tile. Within the Formula tile, you can choose to create a new field or overwrite an existing field. You can expand the formula editor, which will allow you to see a list of all the functions that are available to you. You can add fields to your formula by clicking on them in the column list, or by typing them in and selecting them from the auto-complete list.
Another option to perform a calculation is within the Analyzer. Within the Analyzer, they are referred to as Beast Modes and Calculated Fields. To add a formula, click on Add Calculated Field, which will open up the same formula editor that we saw in the Formula tile in Magic ETL. You may notice that the formula list is not as comprehensive in this editor, but all of the same functions are still available to use when you type them in. When you create a new calculation, you have the option to click the checkbox that says, “Share Calculation on Dataset”. Checking this box will make this calculation available on any other card that uses the same dataset. It is highly recommended that you always check this box so that you don’t have to copy and paste the same formula into every card that you create for the same dataset.
There are a lot of calculations that you can perform within the formula editor. For further reference, please read these Domo knowledgebase articles: