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

[dailyfood]

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

[jonvitale · Accepted solution]

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)