topic Re: Count distinct Cumulative in QlikView

Count distinct Cumulative

Wed, 28 Oct 2015 13:36:15 GMT

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 :

And I want to have this "result" :

For 2013_Q1 : count distinct customer from 2013_Q1

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

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

Thank you.

sunny_talwar — Wed, 28 Oct 2015 14:38:00 GMT

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)))

Wed, 28 Oct 2015 16:05:52 GMT

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 :

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 — Wed, 28 Oct 2015 16:18:11 GMT

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

sunny_talwar — Wed, 28 Oct 2015 16:29:05 GMT

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:

Wed, 28 Oct 2015 16:31:48 GMT

In quarter year.

Thu, 29 Oct 2015 09:51:22 GMT

Thank you for your help