2 Replies Latest reply on Oct 19, 2016 12:17 PM by Francesca Cristoforetti

# Running sum at fixed level of detail

Hi all,

I have some data about customers purchases and I would like to analyze my customers dividing them in three groups based on order count and average amount of each order for each customer. I would like to define order count and average order amount as cumulated values, from the beginning to the current month at [customer_id] level, but I can't understand how I should do.

As you can see in Sheet1, I succeeded in showing running order count and average amount per order when I'm including [customer_id] field in sheet. In Sheet1, I also used the field [Customer Bands] (which define the three groups: L, M, H) as color mark to show that a customer can change group during time. I have currently defined my [Running Amount] and [Running Order Count] as simple RUNNING_SUM and then [Running avg amount per order] as [Running Amount]/[Running Order Count] , as you can see in my woorkbook.

Now I would like to remove customer_id from worksheet because I would like to show just a cumulated customer count ([Running Customer Count]) for each month and for each Customer Group. In Sheet2, for each month, I have already the cumulated customer count. I would like to add the division of this count in my three categories.

NB: I have defined [Running Customer Count] as

WINDOW_SUM(

COUNTD(

IF [date]={FIXED [customer_id] : MIN([date])}

THEN [customer_id]

END),FIRST(),0)

in order to count each cusotmer just one time, maybe this is not compatible with the thee [Customer Bands] definition...

I tried to redefine [Running Order Count] and [Running Amount] as:

{ FIXED [customer_id] : RUNNING_SUM( COUNTD([order_id]) ) }

and

{ FIXED [customer_id] : RUNNING_SUM( SUM([amount]) ) }

But an error says that "Level of detail expression cannot contain table calculation or the ATTR function"

PS: In my workbook I have defined fields also at fixed level of detail ([customer_id]) without RUNNING_SUM, but using this fields that I'm use just the current classification of my customers, including all orders and this is not what I would like to do.

Any other ideas? Thanks