topic Cumulative count distinct in QlikView

Cumulative count distinct

Wed, 15 Dec 2010 20:16:07 GMT

Hi,

I have a count of distinct customers by month. I need a straight table with month as dimension and # of distinct customers visited till that month.

Basically its a cumulative count distinct function. Please advice how to solve this.

Regards,

Kiran.

Cumulative count distinct

Wed, 15 Dec 2010 20:44:20 GMT

Are you unable to use the built-in Accumulation functionality? I did a quick test and accumulation seems to work fine on count distinct in a Straight Table.

Otherwise, you may be able to use something like:

count(distinct total if(VisitMonth<=Month and VisitYear<=Year, CustomerID)

You didn't give any information on what fields you were using for the dates and customer, so I guessed.

Cumulative count distinct

Thu, 16 Dec 2010 05:56:12 GMT

Thanks for your inputs. I tried accumulation but it didnt work. I attached the sample here for more details.

Regards,

Kiran.

Cumulative count distinct

Anonymous — Thu, 16 Dec 2010 06:16:46 GMT

Remove MonthID as a dimension and the go to the Sort tab and set MonthDescr to sort by expression, MonthID, ascending. Then you should be fine.

Cumulative count distinct

Thu, 16 Dec 2010 06:27:16 GMT

Did as suggested. The result is the following:

Jan	2
Feb	3
Mar	5
Apr	8
May	12
Jun	16

But the expected result is:

MonthDesc	#Customers
Jan	2
Feb	2
Mar	3
Apr	3
May	4
Jun	4

My expression should be number of distinct customers visited till that month.

Regards,

Kiran.

Cumulative count distinct

Anonymous — Thu, 16 Dec 2010 06:57:28 GMT

Ok, that was a little bit trickier.

I put together a solution. Check out the attached example. I added a field in the script called FirstVisit and use that as a flag set to either 1 or 0. Then I just sum up the FirstVisit flag with full accumulation. I had to do a binary load of your file as I don't have the data sources but you can just add the field in your script after sorting the table accordingly.

(Basically I sort the table on MonthID and CustomerID so that when the CustomerID changes I tag that row with the FirstVisit for later use)

Cumulative count distinct

Thu, 16 Dec 2010 07:11:04 GMT

Wow, thats works great. Thanks a lot Johannes.

Cumulative count distinct

Thu, 16 Dec 2010 07:13:57 GMT

This is a great solution. But I have a huge datamodel and its will be difficult to do this. Is that a workaround without a datamodel modification?