Help with Record Numbering

[Anweinbe]

UPDATED: I figured it out! the TILE WIDGET!!! Very easy. Sorry everyone for the clutter. Hope this helps others!!!

Good morning all.

I have a sorted file that shows me how many products are sold by week. I want to create a new field that takes the averages of the last 6 weeks of data only. I don't want to hardcode these field names as that could cause issues when I update my data. I always want the last 6 weeks of what is in the dataset. 

Here is what I am thinking in my head that should work.. I thought that if I have 20 records for the product, I could sort the dates descending and then tag the first record as 1, second as 2 etc., and restart the count at the next product. Set a filter on records <7 and then crosstab the results. Then make a new field that averages 1-6. 

My issue: How do I apply a tag to show the 1, 2, 3 down the list restarting at a product change? Probably a simply tool I am overlooking!

Thanks so much!

[SPetrie]

Multi-Row tool. Group it by the product and add 1 to the item above it

Grouping can mess with your sorting, so you can also check if the product above is different than the current product and increment that way as well.

[Anweinbe]

Thank you for the reply. Do you suggest over the Tile Widget that I used? Perhaps they both work?

[DataNath · Accepted solution]

Hey @Anweinbe, rather than needing to create an ID and then filter based upon that, you can just sample the top N records. Once you've sorted by your date, just use a Sample tool and take the first 6 records, ensuring that you group by your Product:

Example attached.

[Anweinbe]

That does work too, however if I want to report on different averages that could get messy. This does help me in a different area of my workflow though.

I found the Tile tool which seems to be working as long as I sort correctly first. Have you used that before? Trying to determine what drawbacks exist on it if any.