Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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!
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.
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!
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 |