2 Replies Latest reply on Nov 29, 2018 9:54 AM by Tijan Senghore

# can you summarize by Running Total?

I'm trying to categorize my sales rep into Tier groups based on customers acquired.

0-10 = Tier3

11-20 = Tier2

>=21 = Tier1

I've created a running total to determine when they go from Tier3 to Tier2 then Tier1

 Rep ID Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 xxx Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 Tier 1 1 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 12 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 30 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 32 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 3 Tier 2 Tier 2 Tier 2 Tier 2 Tier 2

My problem is trying to figure out how many reps are in each Tier group on a monthly basis as follows:

 Rep ID Jan-17 Feb-17 Mar-17 Apr-17 May-17 Tier1 1 1 1 1 1 Tier2 0 0 0 0 0 Tier3 2 2 3 3 3

I seem to be getting this instead:

 Month of NewCustDateForTierGroup Tier 1 January 2017 7855 February 2017 4589 March 2017 4020 April 2017 1769 May 2017 1578 June 2017 1427 July 2017 1070 August 2017 1218 September 2017 912 October 2017 1046 November 2017 762 December 2017 638 January 2018 842 February 2018 766 March 2018 776 April 2018 766 May 2018 914 June 2018 671 July 2018 583 August 2018 462 September 2018 478 October 2018 500

If I can get this solved, I would also like to know how many new customers are in each Tier group monthly.  Packaged workbook is attached.

• ###### 1. Re: can you summarize by Running Total?

Tijan,

For the first part, I'm wondering if it might be useful to make a separate calculated field for each Tier,

for example, for Tier1:

WINDOW_SUM(IF [Running Sum Customer Count]>=21 THEN 1 ELSE 0 END)

These three would be placed on the Measure Shelf with Table Calculation Settings shown below.

This creates many copies of the value, but this can be filtered down to one with INDEX() on the Filter Shelf.