Counting customers that have ordered in x categories

[#1]

Hi

This is probably a very simple question but i am quite new to QV so any help would be appreciated.

I need to create a bar chart that counts distinct customers that have ordered in 1 - 13 categories.

Would I write this in the expression or create a calculated dimention?

Any example of how this might be written woudl be really helpful.

thanks

Heather

[Gysbert_Wassenaar]

Maybe count({<customer={'=count(distinct categories)<14'}>}distinct customer). If not, please post an example document with some data and what the resulting counts should be.

[richard_chilvers]

Hi

I would have tried setting category as the (only) dimension (with a selection of the correct categories as necessary) and a single expression COUNT(DISTINCT CUSTOMER). If you're new to QlikView, the Chart wizard will guide you through.

But, as Gysbert suggests, if this doesn't work well, then post us some examples of what you've tried.

[#4]

Hi

Thanks for the responses. I'll try and explain myself a bit better. I need the bar chart to show 13 bars, numbered 1 to 13, each column must represent the number of categories a customers has purcahsed in in the current year.

it doesnt matter if the categorys are different i.e if some one has bought Stationery and IT, and someone else has bought Art and Cleaning, they woudl both appear in the '2' column. I think I need to create buckets in a calculated dimention as Category will be a filter so the chart needs to reflect selections made here too.

Does that make more sense?

[richard_chilvers]

Hi Heather

Yes, I do understand the issue now, I think.

I don't have an obvious solution; looks like it might need a calculated dimension and I'm no expert on those. But I might take a look when I get 5 minutes

[#6]

Thanks Richard

[#7]

I can create the following table using these expressions;

2015               count({<[Year] ={$(vMaxYear)}>} distinct Category)

2014 YTD       count({<[Year] ={$(vPrevYear)}, [Financial Week #] = {'<=$(vMaxWeek)'}>} distinct Category)

2014 Full        count({<[Consortium Year] ={$(vPrevYear)}>} distinct Category)

Which gives me this:

Customer	2015	2014 YTD	2014 Full
21	0	0	1
22	0	0	1
24	0	0	4
29	0	0	3
30	0	0	1
41	0	0	2
50	3	0	2
51	0	0	2
52	0	0	8
53	0	0	3
58	2	0	3
59	0	0	3

etc

From there you can seet the number of customers that have purchased  in 'x' categories;

Categories	2015	2014 YTD	2014 Full
0	18872	17521	850
1	3927	4676	10631
2	2462	2546	3740
3	1980	2089	2832
4	1688	1765	2433
5	1406	1441	2175
6	1040	1118	1896
7	787	878	1699
8	553	599	1582
9	332	371	1568
10	188	205	1503
11	69	90	1467
12	12	17	940

I need to create this (exclusing the count for customers purchasing in 0 categories)

[flipside]

Hi Heather,

You've lost me with this new information, but with my small dataset I think I'm getting what you originally needed.

Calculated dimension: =aggr(count(distinct Category),Customer)

Expression: =count(distinct Customer)

I'm not sure how it would fit with the dates, possibly three expressions with the set analysis as you have above.

flipside

[#9]

Hi, thanks for the reply, i've tried this but it puts the customer in the bucket of the maximum number of categories they're purchased in regardless of year. (even when i specift the year in the expressions).

[flipside]

Yes, it gets complicated when years are added in. I've tried lots of different combinations in the expression and get close but not close enough. I wonder if creating a new field in script which is a combo of year and category will stop it calculating the max number of categories.

flipside