Cumulative distinct customer

[#1]

Hi

I have a table of  connexion to a web site :

Date ,  IdCustomer

I want to create a chart by month  where  I count  how many distinct  customer I have from the start.

For instance,

    in june, I want to know how many  distinct customers from the starting month to june

    in july, I want to know how many  distinct customers from the starting month to july

Thanks in advance for your support

JJ

[swuehl · Accepted solution]

Jean-Jacques,

at first, I thought that you could do this with a rangesum and above (like you would do for a standard cumulative value), but since you want the count of distinct customers across multiple months, that will not be the solution.

I quite simple solution however might be to create a data island for the MonthEnd (I called it MonthChart) and then do a

=count(distinct (if(Date <= MonthChart,Customer) ))

as expression.

You could probably synchronize selections in MonthChart and your master calendar to get the same look and feel when you select on the chart dimension values (or the master calendar Month).

Please see attached.

Stefan

[#3]

Thanks Stefan.

It works very well. I like this simple solution using a data island.

I thought a set analysis was the way.

By the way,  do you have a solution  with set analysis without creating MonthChart field ?

Thanks again

JJ

[swuehl]

Hm,

like limiting the date range in the set analysis, like

count({<Date = {"<=MONTHEND"}>} distinct Customer)

MonthEnd would need to consider the current dimension value, right? I don't think that's possible with set analysis.

You could create separate expressions per month though, this should work.

I would really like to have a QlikView function that allows to retrieve the current dimension value, this should allow us to use total but still have the current dimension value for comparison, like

count(total distinct if( Date <= GetCurrentDimensionValue( MonthEnd ),Customer )

[#5]

Totally agree with you, we need such function !

see you Stefan

JJ