How to Calculate SUMIF in TM1

IBM does not provide a native function to calculate SUMIF in TM1. How do I calculate SUMIF in TM1 and perform a sum of values based on a user input?

How to Calculate SUMIF in TM1 based on User Input

Let’s say I want to calculate sum of the values based on the data entry by the users over a year and for the Budget version. I have a cube called ‘Budget Calculation’ with the below dimensions.

• Version
• Time
• Branch
• Account
• Index
• Budget Calculation Measure (There are 3 measures – Value (Numeric), Branch (String), Account (String))

The aim of this cube to allow users to enter their budgets in the ‘Budget Calculation’ cube and TM1 will automatically perform a sum of values based on the criteria or data entered by users. So in the example below, we would get Planning Analytics to sum for Branch 101 – Sydney and Account 1000 – Cash.

How to Calculate Sum of Values Based on User Input

• Create Input elements in Time, Branch & Account dimensions
  First of all we need to have Input elements in Time, Branch & Account dimensions, to all the users to perform a data entry in the cube. We have created ‘Input’ element in Time dimension, ‘No Branch’ element in Branch dimension & ‘No Account’ element in Account dimension, for the users to perform their budget inputs (if you need to understand the use of the “No’s” in TM1, please see this post).

• Create Picklist for users to select Branch & Account
  Secondly, we created a picklist for the users to select the Branch & Account elements when they are entering the budgets.

• Write a rule like the following:

SKIPCHECK;

[ 'Version':'Budget', 'Budget Calculation Measure':'Value' ] = N:
  IF(!Branch @<> 'No Branch'
     &
     !Account @<> 'No Account'
     &
     DIMIX('Account',!Account) = DIMIX('Account', DB('Budget Calculation', !Version, 'Input', 'No Branch', 'No Account', !Index, 'Account'))
     &
     DIMIX('Branch', !Branch) = DIMIX('Branch', DB('Budget Calculation', !Version, 'Input', 'No Branch', 'No Account', !Index, 'Branch'))
     ,
     ['Account':'No Account', 'Branch':'No Branch', 'Budget Calculation Measure':'Value']
     ,
     CONTINUE);
   
FEEDERS;

[ 'Version':'Budget',
 'Account':'No Account',
 'Branch':'No Branch',
 'Budget Calculation Measure':'Value' ] =>
  DB('Budget Calculation',
          !Version, !Time, 
          DB('Budget Calculation', !Version, 'Input', 'No Branch', 'No Account', !Index, 'Branch'),
          DB('Budget Calculation', !Version, 'Input', 'No Branch', 'No Account', !Index, 'Account'),
          !Index, 'Value');

• Provide a cube view for user to perform data entry in the cube
  Lastly, we need to create a cube view where the users can perform data entry by selecting the relevant Branch, Account and enter the required values. The Branch & Account selections need to made against No Branch, No Account & Input elements created in the first step. From there the data will be rule driven out to the selected Branch and Account.

• TM1 will automatically calculate the sum of values based on the data entered by the user within the same cube.
  Note the data below is now against Branch 101 and Account 1000.

Want to Know More about SUMIF in TM1?

We’re passionate about TM1 and ready to help. Please reach out if you would like to discuss calculating SUMIF in TM1 or any other topic in TM1, using the form below.