Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Cumulative distinct customer

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

JJ

1 Solution

Accepted Solutions
MVP

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

Stefan

4 Replies
MVP

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

Stefan

Not applicable
Author

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

MVP

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 )

Not applicable
Author

Totally agree with you, we need such function !

see you Stefan

JJ

Community Browser