Dynamically Change of Ranked list with change in week (Moving Dash Board)

[sarad agarwal]

Hi,

I am Trying to create a weekly moving dashboard for ranked values. I have a clue of how can i get the top three ranked validations from a list , but i am not able to link these validations to change with my week.
Source Table:

Week 6	Week 7	Week 8
Active inactive listing	0	Active inactive listing	1	Active inactive listing	1
Create listing	4	Create listing	30	Create listing	10
Create variation relationship	1	Create variation relationship	2	Create variation relationship	5
Modify existing listing	5	Modify existing listing	3	Modify existing listing	0
Modify variation relationship	1	Modify variation relationship	0	Modify variation relationship	0
Unsuppress existing listing	1	Unsuppress existing listing	1	Unsuppress existing listing	0

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Target Table:

Week 9	Week 10	Week 11	Week 12
Top Three Validation	Count	Top Three Validation	Count	Top Three Validation	Count	Top Three Validation	Count
Modify existing listing	81	1		1		1
Create listing	40	2		2		2
Unsuppress existing listing	0	3		3		3

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

The source table will keep adding the weekly values and all the validations that occurred for that week . I want a moving dashboard which gives me data for last 4 weeks from the source table but i want only top three validations based on their count. How can i link the weeks to change dynamically ?

 

[oldbrewer]

				week1	week2	week3	week4	week5	week6
			red	4	1	3	8	3	4
			blue	2	8	4	1	7	3
			yellow	5	7	6	2	6	2
			pink	7	9	7	3	5	8
			green	6	4	8	5	9	9
			problem statement - obtain top 3 scores for weeks 3 to 6
				week1	week2	week3	week4	week5	week6
			red	4	1	3	8	3	4
			blue	2	8	4	1	7	3
			yellow	5	7	6	2	6	2
			pink	7	9	7	3	5	8
			green	6	4	8	5	9	9
				7	9	8	8	9	9
				6	8	7	5	7	8
				5	7	6	3	6	4
				current week = week6
						3	4	5	6		######
						week3	week4	week5	week6
						green	red	green	green
						pink	green	blue	pink
						yellow	pink	yellow	red
						formula giving green in week 6
						=OFFSET(J$16,MATCH(OFFSET(J$16,6,0),J$17:J$21,0),-J$28)
						the 6 becomes 7 and 8 for pink and red
						the numbers in row marked #### are for simplicity in the formula
						you can have ALL ratings for ALL weeks in the bottom table
						use an offset match approact to only "dashboard" the last 4 columns

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>