The Formula Wizard is a step-by-step guide designed to help new modelers master the BY modifier.
⚠️ Important
Members must have the Formula Playground permission and Data Access Rights of Read to Transaction List to be able use the Formula Wizard.
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 Transaction List. 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 and data type are automatically assigned based on the formula. This is because the Formula Playground is in 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 Transaction List
First you select the Transaction List that has the data 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 the List that you want to work with. You see a list of all the Properties from that List. Next to the Property, you see an icon showing the data type. You can also hover over the Property to see the data type.
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 data type.
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, see Aggregate data in formulas. The most common method when using integers or 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 define 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 is the structure of the results. It also determines 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 Property with Dimension data type for each of those Dimensions.
After you have selected all the Dimensions you wish to use, click the View formula results button.
ℹ️ Note
If your transaction data is formatted by date and you want to transform your data into a Month, Quarter, or Year, the TIMEDIM function can convert your dates into a time Dimension.
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 can select the Create Metric button and it creates a new Metric with the correct data type and Dimensions.
🎓 Pigment Academy
To get hands-on practice with the Formula Wizard, check out the Formula Wizard Activity in the Read and Interpret Learning Path in our Academy.