Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

You can succeed best and quickest by helping others to succeed. Join the conversation.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- How can I create this pivot table? (Data with exa...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

dailyfood

Contributor II

2017-11-20
03:02 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

529 Views

1 Solution

Accepted Solutions

jonvitale

Creator III

2017-11-20
04:01 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

1 Reply

jonvitale

Creator III

2017-11-20
04:01 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)