Guide

# Pigment in Action: How to calculate streak cumulations

• 0 replies
• 280 views

Userlevel 3
+4
• Community Manager
• 9 replies

## Introduction

Are you trying to count an unbroken streak of values and require a reset of the count when there is a gap in the monthly data? Cumulations can be expensive operations, so it's important to find an efficient solution. In this community article, we'll explore a solution to this problem so that you can achieve your modeling goal.

## Example Scenario of a Streak Cumulation

Imagine you are currently counting the consecutive months in which your customers have placed orders with you. You have access to the following data:

• A transaction list that includes your customers' orders.
• A metric that indicates the number of orders per customer per month, derived from the transaction list.

Your goal is to determine the order streak for each customer. You can then use this information to inform a loyalty program and offer discounts, enhancing customer loyalty

## Let’s build a Solution!

In this metric, there is a count of the number of orders that the customer makes in a given month. ‘Order Count’ is defined by the formula ‘'Orders by Customer'.'Invoice Value'[BY COUNT: 'Orders by Customer'.Customer, 'Orders by Customer'.'Invoice Month']’. This formula counts the number of orders that have an Invoice Value per Customer and Month as defined in the transaction list ‘Orders by Customer’.

NOTE: values that are BLANK are not included in the count.

Here, we are generating a flag if there is a value defined in a given month for each customer to show that at least one order was made. To do this, we can simply use the formula ‘ISDEFINED('Order Count')’. This formula returns a value of TRUE whenever there is a value defined in the metric ‘Order Count’. If there is no value (i.e. the source cell is BLANK) then this function also returns a value of BLANK.

NOTE: If there is a value of 0, this will still return TRUE, as 0 is still considered a defined value.

In the below metric, we are finally generating our order streak cumulation!

To do this, we are generating a value of 1 when we have flagged that a customer makes at least one order in a given month. We then want to add this to our cumulation to increase the total.

Then, the PREVIOUS(Month)['Customer Has Order'] term of the formula drives the cumulation. The key to ensuring the reset of the cumulation is to also apply a filter to the PREVIOUS(Month) term. The result of this is that the cumulation will only apply when there is a month with an order to cumulate. For months where there are no orders, the PREVIOUS(Month) is disregarded. In addition, if there are no orders, there is no value to add, and therefore the overall result will be 0, thus resetting the cumulation.

You can see here that the customer ‘Umbrella Corporation’ broke their order streak in July, and their streak was reset when they made another order in August.