guage chart for top n items

[#1]

I have data in this way:

Shop	Item description	Quantity	Date	Sale
A	Item1	100	Jan-13	10
A	Item1	100	Feb-13	9
A	Item1	100	Mar-13	9
A	Item2	200	Jan-13	8
A	Item2	200	Feb-13	6
A	Item2	200	Mar-13	6
A	Item3	700	Jan-13	6
A	Item3	700	Feb-13	9
A	Item3	700	Mar-13	5
B	Item1	700	Jan-13	2
B	Item1	700	Feb-13	9
B	Item1	700	Mar-13	8
B	Item2	100	Jan-13	3
B	Item2	100	Feb-13	1
B	Item2	100	Mar-13	5
B	Item3	80	Jan-13	3
B	Item3	80	Feb-13	3
B	Item3	80	Mar-13	9

I want to have guage charts which only display the top 2 items when selecting 1 shop...

I'm thinking of to create a table like below and to hard code to select index 1 and 2 in 2 separated guage charts by set analysis, my question is how to create the index table, or if there are better options to do so?

Shop	Item description	Index
A	Item1	3
A	Item2	2
A	Item3	1
B	Item1	1
B	Item2	2
B	Item3	3

The result will be having 2 guage charts displayed, when selecting shop A, Item2 and Item3 (with index 1 and 2) will be selected; and when selecting shop B, Item1 and Item2 (with index 1 and 2) will be selected instead...

[#2]

You say you are looking to create a guage chart, but what do you want to show in the gauge chart?  From your description, it sounds like you want to show the top 2 items, which would not be a gauge chart.  See if this is what you are looking for.

[manas_bn · Accepted solution]

Kenny,

You can use the below code to get the Index field. You can use set analysis to get the two gauge charts.

LOAD *,
Item&Shop as ID, //Key Field
Quantity*Sale as TotalSales //Calculate Total Sales
INLINE [
    Shop, Item, Quantity, Date, Sale
    A, Item1, 100, 13-Jan, 10
    A, Item1, 100, 13-Feb, 9
    A, Item1, 100, 13-Mar, 9
    A, Item2, 200, 13-Jan, 8
    A, Item2, 200, 13-Feb, 6
    A, Item2, 200, 13-Mar, 6
    A, Item3, 700, 13-Jan, 6
    A, Item3, 700, 13-Feb, 9
    A, Item3, 700, 13-Mar, 5
    B, Item1, 700, 13-Jan, 2
    B, Item1, 700, 13-Feb, 9
    B, Item1, 700, 13-Mar, 8
    B, Item2, 100, 13-Jan, 3
    B, Item2, 100, 13-Feb, 1
    B, Item2, 100, 13-Mar, 5
    B, Item3, 80, 13-Jan, 3
    B, Item3, 80, 13-Feb, 3
    B, Item3, 80, 13-Mar, 9
];

//Group the Items and Shops and get Cummulative Sales for the three months
Ranking:
NoConcatenate
LOAD Item&Shop as ID,
Item,Shop,
Sum(TotalSales) as Cummulative
RESIDENT Fact
group BY Item,Shop  ;

//Rank the Item Shop combinations based on Cummulative Sales
left join(Ranking)
LOAD
ID
,if(Shop=Previous(Shop),peek(Rank)+1,1) as Rank
RESIDENT Ranking
ORDER BY Shop,Cummulative desc;

drop fields Item,Shop from Ranking;

Table structure will be like this.

Gauges show sales of ALL Rank 1 and Rank 2 items when no shop is selected. When a shop is selected, it will show the Rank 1 and 2 items for that shop.

Cheers!

[manas_bn]

Ohh and for shop B, if you go by sales top 2 items will be Item1 and Item3... So your table will be

Shop	Item description	Index
A	Item1	3
A	Item2	2
A	Item3	1
B	Item1	1
B	Item2	3
B	Item3	2