How can I create this pivot table? (Data with example included)

dailyfood — Mon, 20 Nov 2017 20:02:03 GMT

Hello,

I want to create similar pivot table with the main dimension being the different combinations of quantities certain item appears in purchase orders and the probability of the secondary item to appear. this is similar to basket analysis.

here is the desired output:

			Secondary 1
			0	1	2	3	4+	% of Orders W Secondary 1
Total (CAT1)	Large CAT1	Medium CAT1
0	0	0	%	%	%	%	%	%
1	1	0	%	%	%	%	%	%
1	0	1	%	%	%	%	%	%
2	2	0	%	%	%	%	%	%
2	0	2	%	%	%	%	%	%
2	1	1	%	%	%	%	%	%
3	1	2	%	%	%	%	%	%
3	2	1	%	%	%	%	%	%
3	3	0	%	%	%	%	%	%
3	0	3	%	%	%	%	%	%
4	1	3	%	%	%	%	%	%
4	2	2	%	%	%	%	%	%
4	3	1	%	%	%	%	%	%
4	4	0	%	%	%	%	%	%
4	0	4	%	%	%	%	%	%
5+	...	...	%	%	%	%	%	%

I was able to get the first column (Total CAT1) using the following calculated dimension:

=aggr(Ceil(sum(QUANTITY)),%Order_Key,MAIN_CATEGORY)

but I don't know how to get the combination of Large , Medium and Small that adds up to the Total CAT1

you can find sample of the data attached

Re: How can I create this pivot table? (Data with example included)

jonvitale — Mon, 20 Nov 2017 21:01:38 GMT

DF,

Even though you are getting the right first column, I suspect that your aggregation function isn't doing exactly what you think. Since it's aggregating on all of Main_Category, it's also counting "Secondary 1". It just so happens that because there are many more CAT1 in the data than Secondary 1, you are getting each possible dimension value in your table that you need.

What would happen if you tried a set analysis in "sum(QUANTITY)"?

Does this give the same first column as your current code:

aggr(Ceil(sum({<MAIN_CATEGORY={'CAT1'>}Quantity) ),%Order_Key,MAIN_CATEGORY)

Does this give the 2nd column? (and similar for third?)

aggr(Ceil(sum({<ITEM_FAMILY_GROUP={'Large CAT1'>}Quantity) ),%Order_Key,MAIN_CATEGORY)

topic How can I create this pivot table? (Data with example included) in QlikView

How can I create this pivot table? (Data with example included)

Re: How can I create this pivot table? (Data with example included)