Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dailyfood
Contributor II
Contributor II

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

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


1 Solution

Accepted Solutions
jonvitale
Creator III
Creator III

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)


View solution in original post

1 Reply
jonvitale
Creator III
Creator III

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)