Count distinct Cumulative

[#1]

Hello all,

I need some help to resolve a problem to calculate a cumulative count distinct.

I want to calculate how customers I have from the beginning till the last quarter/year.

I load this data :

Quarter_year	Customer	Sales
2013_Q1	A1	1
2013_Q1	A2	2
2013_Q1	A3	3
2013_Q1	A1	4
2013_Q2	A1	5
2013_Q2	A3	6
2013_Q2	A4	7
2013_Q3	A1	8
2013_Q3	A5	9
2013_Q3	A6	10
2013_Q4	A1	11
2013_Q4	A6	12
2013_Q4	A7	13
2014_Q1	A3	14
2014_Q1	A4	15
2014_Q1	A8	16
2014_Q2	A3	17
2014_Q2	A4	18
2014_Q2	A8	19
2014_Q2	A9	20
2014_Q2	A10	21

And I want to have this "result" :

Quarter_year	Result
2013_Q1	3
2013_Q2	4
2013_Q3	6
2013_Q4	7
2014_Q1	8
2014_Q2	10

For 2013_Q1 : count distinct customer from 2013_Q1

uarter_year	Customer	Sales
2013_Q1	A1	1
2013_Q1	A2	2
2013_Q1	A3	3
2013_Q1	A1	4

For 2013_Q2 : count distinct customer from all customers in  ( 2013_Q1 and 2013_Q2)

Quarter_year	Customer	Sales
2013_Q1	A1	1
2013_Q1	A2	2
2013_Q1	A3	3
2013_Q1	A1	4
2013_Q2	A1	5
2013_Q2	A3	6
2013_Q2	A4	7

For 2014_Q1 : Count distinct customer from all customers in (2013_Q1, 2013_Q2, 2013_Q3, 2013_Q4, 2014_Q1°

Quarter_year	Customer	Sales
2013_Q1	A1	1
2013_Q1	A2	2
2013_Q1	A3	3
2013_Q1	A1	4
2013_Q2	A1	5
2013_Q2	A3	6
2013_Q2	A4	7
2013_Q3	A1	8
2013_Q3	A5	9
2013_Q3	A6	10
2013_Q4	A1	11
2013_Q4	A6	12
2013_Q4	A7	13
2014_Q1	A3	14
2014_Q1	A4	15
2014_Q1	A8	16

Thank you.

[sunny_talwar]

See if the attached helps:

Expression:

=If(KeepChar(Only({<Quarter_year = >}Quarter_year), '0123456789') <= Max(TOTAL KeepChar(Quarter_year, '0123456789')),

Count({<Quarter_year = >}Aggr(Count({<Quarter_year = >}Customer), Customer)))

[#3]

Thank you for your help.

It works when I use the function "KeepChar(Quarter_year, '0123456789')".

In my application, I transform quarter year into number like : left(Quarter_year,4)*4+right(Quarter_year,1) :

the quarter_year_num  let me compare data for différent quarter year.

example :

Quarter_year	Quarter_Year_Num
2013_Q1	8053
2013_Q2	8054
2013_Q3	8055
2013_Q4	8056
2014_Q1	8057
2014_Q2	8058

when I use this expression, it doesn't work :

=If(Quarter_Year_Num <= Max(TOTAL Quarter_Year_Num),

Count({<Quarter_year = >}Aggr(Count({<Quarter_year = >}Customer), Customer)))

[sunny_talwar]

And where are you making selection for quarter? In Quarter_year or Quater_Year_Num list box?

[sunny_talwar · Accepted solution]

New Expression:

=If(Only({<Quarter_year = , Quarter_Year_Num = >} Quarter_Year_Num) <= Max(TOTAL Quarter_Year_Num),

Count({<Quarter_year = , Quarter_Year_Num = >}Aggr(Count({<Quarter_year = , Quarter_Year_Num = >}Customer), Customer)))

NOTE: Make sure 'Full Accumulation' in checked:

[#6]

In quarter year.

[#7]

Thank you for your help