Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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

Thanks in advance for your support

JJ

1 Solution

Accepted Solutions
swuehl
MVP
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).

Please see attached.

Stefan

View solution in original post

4 Replies
swuehl
MVP
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).

Please see attached.

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

swuehl
MVP
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