Calculations are available starting from the Small business plan.
Slemma gives you the ability to create calculated fields right inside the tool. This gives you the freedom to create the measures and dimensions you need right when you need them.
You can create calculated fields by defining a formula that is based on existing fields and other calculated fields, using standard functions and operators.
In Slemma, you can:
- Add calculations on the Dataset level (using SQL)
- Add calculations in the Chart editor (using MDX)
What’s the difference?
- Calculations made in the Dataset editor are available for all charts built on this dataset.
Calculations made in the Chart editor are available for the charts that are on the current dashboard. - In the Dataset editor, you can create both measures and dimensions of “string” type.
In the chart editor, you can create calculated measures only. - There is also a difference in order of calculations and aggregations in these two methods:
- For calculations made in the Dataset editor, the column is first calculated according to the formula, then the measures are aggregated.
- For calculations made in the Chart editor, measures in the formula are aggregated, then the formula is calculated
For example, we have a dataset like
Let’s assume that we want to create a formula: M3 = m2/m1 and to compute a SingleValue.
For dataset calculations, it first counts new values:
And then when you create a Single value chart, the system aggregates them:
M3 = 5 + 6 + 2 = 13
While chart calculations first aggregate the fields:
And then calculate the formula:
M3 = m2/m1 = 4.6
Adding Calculations on the Dataset Level
Let’s assume that you want to perform a calculation in the dataset. Slemma enables you to do it by using the Add calculation button at the top right corner of the Dataset editor page (which you can see when creating a new dataset or by clicking the existing dataset >> Edit).
In the appeared window, you may set a name of calculation and insert a desired formula in the Formula box.
If you want to insert a calculation to the dataset based on a database, use SQL functions supported by this DBMS. If you want to add a calculation to the imported dataset, use Postgres syntax of SQL.
Depending on the chosen function, calculations will return the result for each row of the selected column (e.g. Abs) or return a single value, calculated from values in a column (e.g. Max).
After you’ve finished with the formula creation, it will appear in the list of the fields in the dataset. The field type (measure/string) will be defined automatically. For string fields, you can change the type to Geography.
Click on the calculation name to edit the formula.
To delete the calculation, select three dots button at the end of the field row and click Delete.
To change field format or visibility, select three dots button at the end of the field row and click Settings. Depending on the type of the calculated field, the appropriate Field settings window will open.
In the Chart designer, you may use the newly created calculation along with the other dimensions and measures to build a chart.
Open the Dataset Editor and click Add calculation button.
Enter the following formula:
date_part(‘day’, now() – Date)
Replace Date with the name of your date dimension (start typing and you’ll see the list of your fields).
Click OK for the Calculation window. The new column is now added to your dataset.
To edit the formula, just click the field’s name.
NOTE: by default, now() uses UTC Time Zone. To convert it into your time, add + interval ‘X hour’, for example if your Time Zone is UTC+1, use: date_part(‘day’, now() + interval ‘1 hour’ – Expected close date)
Open the Dataset Editor and click Add calculation button.
Enter the following formula:
case when your_dimension= ‘Yes’ then 1 else 0 end
This calculation will add a new measure with values from 0 to 1 to your dataset.
If one of your fields contains ‘null’ value, any mathematical operation will return null. To avoid this, you can use the ‘coalesce’ function:
coalesce(field_name1,0)+coalesce(field_name2,0)
Adding Calculations in the Chart editor
When creating a chart in the Chart designer, after you have selected the chart type, click ADD MEASURE option. Now you have an opportunity to:
1) Select a measure from the measures list from your dataset (including metrics created in the dataset).
2) Use one of our already created formulas by clicking Calculations.
3) Add your own calculation using MDX formulas.
To create a calculated measure, click ADD CALCULATION.
Once you are in the Calculated measure dialogue, you will see all of your measures and dimensions (this will allow you to insert each element’s unique name into your calculation), and all the functions that you can use with their descriptions at the bottom of the dialog window. This makes creating calculations much easier.
Create a unique name for your measure.
Please note that we do not allow to use the following names (including the index at the end like Fixed1):
- Left
- Top
- Fixed
Click the function in the list to see its description.
You can also adjust the format of your measure by clicking Number format button.
To learn about MDX functions, you can visit the official pages.
Examples
Here we’ll describe some of the most commonly used functions.
If you just want to sum up two measures together like [New Users] and [Retained Users], you can just use a plus sign.
The Sum function, however, comes in handy when you want to create a new metric for a subsection of a measure. The form looks like this:
Sum(Set Expression,Numeric Expression)
So, let’s say you wanted the revenue that only comes from a certain plan you offer. It would look something like this:
Sum([Plan.Hierarchy].[Plan].[1],[Revenue])
The Sum function just has two parts. The first is the member that you want it to use and the second is the measure you want it to use.
If you want to have several plans, this is when you would use a Set in place of where we have [Plan.Hierarchy].[Plan].[1]. A set is a list of members all from one hierarchy. A set uses curly brackets { } and each member is separated by a comma.
Sum({[Plan.Hierarchy].[Plan].[1],[Plan.Hierarchy].[Plan].[2],[Plan.Hierarchy].[Plan].[3]},[Revenue])
Now because the first part of the Sum function just needs a set, you can put a whole bunch of other functions in there including the Filter and Except functions. As long as the function returns a set, you can put these functions inside the first part of the Sum function.
The filter function is super useful and like we just said, can be used in conjunction with the Sum function. This is because the Filter function returns a set of members that all that pass some logical test.
The filter function will look like this:
Filter(Set Expression, Logical Test)
Place some hierarchy in the first part and put .Members afterwards. This just says, “Hey, I want you to look at all of the members in this hierarchy and see if it passes my test.” Then you put in a test. The following example returns a set of customers that all made a payment the previous member amounting to more than 0.
Filter([Customer.Hierarchy].Members,aggregate([Date.Hierarchy].[Month].Currentmember.Prevmember,[Revenue])>0)
Place a set in there and it will count the number of members in the set you are giving it. It will look something like this:
Count(Set Expression)
A great application if this is calculating Average Revenue per Paying Customer.
Because we already made a filter function above, we can modify it so that it sees if the customer made a payment in the Currentmember simply by removing the .Prevmember.
Combine the count function with our formula above and you will get the number of customers that made a payment in the Currentmember.
Count(Filter([Customer.Hierarchy].Members,aggregate([Date.Hierarchy].[Month].Currentmember,[Revenue])>0)
Once you have this, all you have to do is divide your total Revenue measure by this function. This will return your ARPPU.
[Monthly Recurring Revenue]/Count( Filter([Customer.Hierarchy].Members,aggregate([Date.Hierarchy].[Month].Currentmember,[Revenue])>0)
To show an average day value for each month, we add a Month level to the chart. This means that we should specify the “day” date level in the formula:
Avg([Your_date_dimension.Hierarchy].[Month].currentmember.children,[your_measure])
currentmember.children means that we’ll take the level which is smaller than the current level shown in the chart.
Please note that if we have more than one value for each day, we’ll count a SUM for this day first, and then the average for all the days.
Let’s imagine you want to change the value of the measure for some of the dimension elements which contains the specific text in the name:
iif([Your_dimension_name.Hierarchy].CurrentMember.name matches ‘.*part_of_name.*’,[your_measure]*0.65,[your_measure])
Let’s consider the following example of a calculated measure with DateDiff function:
iif(
DateDiff(‘seconds’, now(), ParseDate([Date.Hierarchy].[Days].CurrentMember.Caption, ‘yyyy-mm-dd’)) > 0 ,
1,
0 )
This calculation will return 1, if the current date is greater than the date in the current dimension element.
Where:
Replace Date with the name of your date dimension (start typing and you’ll see the list of your fields).
ParseDate([Date.Hierarchy].[Days].CurrentMember.Caption, ‘yyyy-mm-dd’)) recasts the current dimension element to Date type.
‘seconds’ means in which units to calculate the difference.
Possible options:
‘seconds’
‘minutes’
‘hours’
‘days’
‘weeks’
‘months’
‘years’