Help with Record Numbering

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!

Tips and Tricks

Accepted answers

DataNath

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.

Take top 6 records by product.yxmd

All comments

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

Example attached.

Take top 6 records by product.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703