How to Set Up a Count Based on an Attribute

Fwolf
Fwolf
in Best Practices

This example will teach you how to set up a count in order to count items in a source module based on specific attributes in that source module. 

Anaplan does not contain a 'Count' function like Excel, but it’s very easy to simulate the same behavior. All you need to do is add in a line item called 'Count' and set the formula of the line item to be 1. Then create a new module that is dimensioned by the attributes you want to count over and create a SUM off the count. 

Here is a simple example: 

Here we have a numbered list that contains a list of emails sent along with their status. I want to be able to count the number of emails sent for each sender (or receiver) based on the status 'sent'.

Fwolf_1-1580308881088.png

Steps to count: 

  1. Ensure Sender is formatted as type 'List'. 
  2. Ensure Status is formatted as type 'List'.
  3. Add a line item to the source module called 'Count'. Format the line item as a number, and set the formula to 1.
  4. Create a new module called 'Count of Emails' that applies to the email Sender list and the Status list.
  5. Add a new line item to the module for Count and the formula will be: SourceModule.Count[SUM: SourceModule.Status, SUM: SourceModule.Sender]

In this solution, you will also get the count of how many emails failed.

What if I only want to see the count of Sent Emails?

  1. Ensure Sender is formatted as type 'List'.
  2. Add a line item to the source module called Count. Format the line item as a number and set the formula to 'If Status = StatusList.Sent' then 1 else 0'

Note: if Status is formatted as text, this option works the same way. Just set status = "sent" in the if statement.

3. Create a new module called 'Count of Emails' that applies to the email Sender list only.

4. Add a new line item to the module for Count and the formula will be: SourceModule.Count[SUM: SourceModule.Sender]

What if both the sender and status are text values?

This is a more complicated scenario to get an accurate count. Before trying this, evaluate if it’s possible to create a sender list. In this case, we will need to use the RANK function along with sorting of the module to see a count. 

  1. Add a line item to the source module called 'Count'. Format the line item as a number, and set the formula to 1.
  2. Add a line item to the source module called 'Include?'. Format the line item as a Boolean, and set the formula to 'Status = "sent" ' (Note: Because this is a Boolean line item you do not need to include the IF THEN.) 
  3. Add a line item to the source module called Rank. Format the line item as a number, and use the RANK function with the following arguments: 

RANK(Count, DESCENDING, SEQUENTIAL, Include?, Sender) 

The keyword SEQUENTIAL makes it so that tied values are assigned separate rankings, in the order they occur. In this case, it will provide a count for each sender. 

Include? ensures it is ranking only sent emails that have status = sent. 

Sender adds in a grouping element so that it counts each sender individually. 

4. You now have the rank for each sender. The highest Rank for each sender is how many emails they have sent. Sort the module by the Rank, and the highest senders will show up first. 

This solution should be evaluated before using it on a dashboard for a large group of end users because it might be confusing for end users to review this as the lower-ranked senders will also show up. 

 

Tagged:

Comments

Categories