Top 500 items sold and top 5 item sold with each o... - Qlik Community

ankitbisht01 · ‎2017-08-28

Dear Experts,

I am trying to make a Pivot table in which, I need to show the top 500 Items sold and along with those 500 items, I need to show top 5 item sold with each item in top 500.

So the format of my report will be.

Item ID sum(Quantity) ItemID1 ItemID2 ItemID3 ItemID4 Item5

11 5000 70 50 40 30 10

22 4500 90 40 30 22 13

33 4000 170 150 140 130 110

44 3500 700 550 440 330 310

55 4000 750 650 340 230 210

Dimensions: item ID

Expression: Sum(quantity)

I was Able to acehive top 500 by calculated dimension =if(aggr(rank(sum([Sales Quantity])),[Item ID])<=10,[Item ID]).hevie

Not able to achevie next 5 coloumns which shows top 5 sold Item.

Please help

Regards Ankit

Kushal_Chawda · ‎2017-08-28

it it top 500 or 10?

if(aggr(rank(sum([Sales Quantity])),[Item ID])<=10,[Item ID])

effinty2112 · ‎2017-08-28

HI Ankit,

I don't think the layout you suggest will give you what you want unless each of your top 500 items share the same 5 items as the top ranked items bought together.

For each of your top 500 you'll need a column to identify the ID of the top ranked item sold with it then another column to show the amount sold. You'll need a pair of columns for each of the top 5 ranked products bought with the principal item.

What you ask is difficult, perhaps impossible, to do purely in the UI. A scripting solution is possible and so if you could post your data that would give something to work with.

Regards

Andrew

ankitbisht01 · ‎2017-08-28

yup it suppose to be 500 only , was just testing with 10

Kushal_Chawda · ‎2017-08-28

what is logic to calculate Item1?

sunny_talwar · ‎2017-08-28

Would you be able to provide some sample raw data to check this out?

Top 500 items sold and top 5 item sold with each of these 500 items.