How to use the Formula Wizard to master the BY Modifier

The Formula Wizard is a step by step guide that is designed to help new modelers master the BY modifier.  This article will discuss how to properly utilize the Formula wizard. 

Table of Contents

What is the Formula Wizard?

Located within the Formula Playground, the Formula Wizard is a step by step guide that allows Members to create a BY modifier formula from a Transactions lists.  The Formula Wizard allows Members to select a Transaction list property for which they want to group or transform the data with.  They can then select the aggregation method on how they want to group their data.  Lastly, they define the different dimensions they wish to use for defining the structure of their newly grouped data.

How to use the Formula Wizard

After opening the Formula Playground, there are four different steps to help you build out a BY modifier formula.  After creating the Formula, you can then review the results and if you have the proper permissions, create a Metric from the formula. 

The Dimensions needed for structure, data dimension type will automatically be assigned based on the formula.  This is because the Formula playground is put into Automatic structure mode. 

Opening the Formula Wizard

From the Sidebar, click on the Formula Playground.  Next, click on the Open Formula Wizard button located at the bottom.

Step 1: Selecting the Transactions list

First you will select the Transaction List that has the data that you wish to transform or aggregate.  For example in Finance, if could be the List where your ERP data is coming into Pigment.

Once you have selected the list, click the Next step button at the bottom.

Step 2: Choose the property that contains the data.

In step 2, you are choosing the value you from that list that you want to work with.  You will see a list of all the properties from that list.  Next to the property, you will see an icon showing the type of data.  You can also hover over the property to see the type of data contained in there.

For example, if you wanted to create a formula that showcased Units by Product by Country by Month.  You would select the Unit or Volume property from the list, in this example it should be number or integer formatted.  

After you have selected the property you wish to use, click the Next step button at the bottom.
 

Step 3: Choose your Aggregator

For more information on aggregations, review here in Community.  The most common method when using integers of numbers is SUM which adds up the values.  

The options are determined by the format of the property you have chosen.

After you have selected the aggregation method, click the Next step button at the bottom.

Step 4: Choose the Dimension lists to group by

The last step is to defined the Dimensions you want to view your data by. For example, if you wanted to see the Volume by Product by Country by Month. You would then check Country, Month and Product dimensions.

However many dimensions you select, that will be the structure of the results.  It will also determine the level of granularity 

You can select multiple dimensions.  In order for a dimension to be present, it must be used as the format for a property in that list.  

For example, in order to use Country, Month and Product dimensions, there must be a dimension data type formatted property for each of those dimensions.

After you have selected all the dimensions you wish to use, click the View formula results button.

Step 5: View results!

Now that you have your results, you can see your formula at the top and interact with the data.  From here, you can Pivot the data, view it as a chart, and apply other settings, such as filters or formatting.

Members with the Configure Blocks permission will be able to select the Create Metric button and it will create a new Metric with the correct data type and dimensions.