topic Cumulative distinct customer in QlikView

Cumulative distinct customer

Sun, 11 Dec 2011 10:24:24 GMT

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

Re: Cumulative distinct customer

swuehl — Sun, 11 Dec 2011 12:37:25 GMT

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

Cumulative distinct customer

Sun, 11 Dec 2011 13:09:55 GMT

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

Cumulative distinct customer

swuehl — Sun, 11 Dec 2011 14:11:01 GMT

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 )

Cumulative distinct customer

Sun, 11 Dec 2011 17:45:53 GMT

Totally agree with you, we need such function !

see you Stefan