Is there an easy way to sort by week number?

Ask for Help
1

What I would like:

  • Add sales with date
  • Automatically ad corresponding week number
  • So I can sort and compare on week by week basis
    I don’t assume it will be easy, but is there a way for starters?
2

You can start by using a Math column to determine the Week Number from the Date, and take it from there.

Weeknum(Date)
3

Ah ok! That helps!

4

I just did this yesterday, and didn’t know of @Darren_Murphy’s Weeknum function, but managed to get dated Entries sorted into weeks (and also months) for viewing reports of these Entries.

In a helper table set columns for:

  • Row index… just a manually entered incrementing number
  • Start range, math column: now - WEEKDAY(now) +1 -(row-1)*7
  • End range, math column: now - WEEKDAY(now) -(row-2)*7
  • Query Entires by
    • date on or after start range, and
    • date on or before end range

Screenshot 2023-10-05 at 7.41.09 AM
Screenshot 2023-10-05 at 7.41.09 AM1622×1234 148 KB

I also have custom filters to choose these weekly ranges, or monthly ranges, and also by a parent category.

2 Likes
5

Thanks! I don’t know if I would have managed that.
But @Darren_Murphy 's track did the trick.

6

Darren, this is great and easy, thanks.
Can I ask you one more thing?
Say if I wanted to see differences between this week and last week and the week before that so I could make a hit list, could you give me a beginning where and how to start?
Never mind of course if you’re busy!

7

I think you can approach it like this:

  • Create a math column to calculate the current weeknum + year number:

YEAR(N)*10^2+WEEKNUM(N)

  • Create a math column to calculate last week’s weeknum + year number:

YEAR(N-7)*10^2+WEEKNUM(N-7)

With N being the date in question.

Then, you can create a relation + lookup: Relate last week’s math column to current week’s math column, then lookup the sales from last week.

Finally, calculate the difference.

1 Like
8

Thank so much, I am going to try this!

1 Like