Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
it it top 500 or 10?
if(aggr(rank(sum([Sales Quantity])),[Item ID])<=10,[Item ID])
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
yup it suppose to be 500 only , was just testing with 10
what is logic to calculate Item1?
Would you be able to provide some sample raw data to check this out?