topic Re: Help counting/charting occurrences within data in QlikView

Help counting/charting occurrences within data

Wed, 27 Jan 2016 16:22:19 GMT

Hello,

I have a transaction file with two fields: CustomerID and ItemID. Each line represents a single order/transaction, so a customer ID can show up multiple times. My thought was there would be a way to count/show the distribution of how many customers made 1 order, 2 orders, 3 orders, 4 orders, 5 orders...N orders. What I would like is a histogram/bar chart that details this (so first bar would be number of customers that made 1 order, second would be # of customers that made 2 orders, and so on). I have been playing around for a half day trying to figure it out and can't seem to get it. I'm pretty new so any help would be much appreciated!

Thanks!

Re: Help counting/charting occurrences within data

Gysbert_Wassenaar — Wed, 27 Jan 2016 16:45:32 GMT

You need a calculated dimension for this =aggr(count(ItemID),CustomerID). And the expression to go with that is count(distinct CustomerID).

Re: Help counting/charting occurrences within data

Wed, 27 Jan 2016 16:49:23 GMT

Hi Gysbert. Thanks for this. The chart almost looks perfect, however, it seems that I am missing something as it just lumps a single total into one bar? Do I have to set parameters to delineate multiple bars?

Re: Help counting/charting occurrences within data

Gysbert_Wassenaar — Wed, 27 Jan 2016 17:17:03 GMT

You'll have to show me a qlikview document that shows what you mean. I don't see how it could do that.

Re: Help counting/charting occurrences within data

Wed, 27 Jan 2016 17:40:12 GMT

My bad! I put Class instead of Count. It works now Thank you!!

Re: Help counting/charting occurrences within data

Gysbert_Wassenaar — Wed, 27 Jan 2016 17:54:48 GMT

Haha . Glad you sorted it.

Re: Help counting/charting occurrences within data

Tue, 02 Feb 2016 21:25:36 GMT

Hi Gysbert,

Thanks for your help. I have one follow up question. Since I now have a nice graph which shows customers by number of orders and another graph of sales volume by number of orders, I'd like to be able to slice and dice the rest of my dashboard/metrics by number of orders made. I believe I need to create a list box for the number of orders. I tried playing around with it but wasn't able to figure out how to do this. Is this possible? If so, do you know how I can do that?

Thanks again for helping a new guy out. I'm looking to take some formal training on this ASAP but for now your help is invaluable.

Re: Help counting/charting occurrences within data

Gysbert_Wassenaar — Wed, 03 Feb 2016 15:36:47 GMT

New question, new discussion please. These two documents are worth reading too:

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling