1

I have a master calendar that includes week and weekseq, weekseq is just an autonumber of week and year. I am having an issue with the column labels on a 52 wk report that works of current week back 52 weeks. When i use weekseq as the column header it works out correctly but this header is of no use, Is there a way for me to replace weekseq with week in my column header? I have currently been trying =week(max(WeekSEQ)) for current week but not working out as it should.

Thanks

PA060
  • 59
  • 1
  • 7
  • Hi can you share a file or screenshots as not completely understand to what you are referring to as "column header" – Hubert Dudek Jan 15 '20 at 17:19

1 Answers1

1

for sure :

=week(max(WeekSEQ))

is incorrect as WeekSeq is autonumber and week process date or timestamp field only. So you need to have Date field in your calendar and use it:

=week(max(*date_field*))

(if you don't have date field you can create it but for that I need at least snapshot of your datamodel - you can make screenshot of table viewer which you can access using Ctrl+T)

Hubert Dudek
  • 1,666
  • 1
  • 13
  • 21
  • Hi Hubert, I currently use week(max(Date) for current wk, week(max(Date -7)) for previous wk, -14 for 2 weeks ago etc. The issue is i use broken weeks and wk 53 2019 and wk 1 2020 started within the same week. this means my column titles never show wk 53 and wk 1 its either 1 or the other. – PA060 Feb 12 '20 at 14:31
  • I would just create MasterCalendar (maybe you already have separate table with calendar?) and just numbered 1 as week for your oldest data and then +1 for every week no matter the year, so for example after 2 years you will have week 104 etc. It is simple solution which will remove your problem. – Hubert Dudek Feb 15 '20 at 14:54