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

    Running sum at fixed level of detail

    Francesca Cristoforetti

      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