Jump to content

Find the week before calculation


This topic is 2726 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a table that is a log of weekly records, and when I'm presenting the current record, I need to be able to pull data from the week before, so I'm currently using a calculated field with a reference to This Week Start Date - 7. But every now and then, the weeks are not a week apart. So what's the best way to find the Previous Week Start Date when it isn't an exact 7 day match?

Thanks!

Link to comment
Share on other sites

Sorry, I should explain further...

Table: WeeklyLog
WeekStart(Date Field)
PrevWeekStart (Calculated Field, returns Date, Calculation = WeekStart-7)

Then I have a table occurence PreviousWeekLog_TO that is setup like this:
804c343fac224d708eeab0c8fc7a011d.png

Then on one of my report layouts I do some calculations between the two tables to show changes from week to week in the data. 
This works perfectly when the two weeks are 7 days apart, which is 90-95% percent of the time. However, sometimes a client will start a new WeeklyLog 8-n days after the PreviousWeekLog->WeekStart. What I would like to do is make my calculated field "PrevWeekStart" be more dynamic. A Client will log for anywhere from 6-36 weeks of data, so I'd like to find the previous WeeklyLog record that the client had (in date order) rather than just assume it was 7 days prior.

This scenario works:
Record 1, WeekStart = 10/1/16
Record 2, WeekStart = 10/8/16

This scenario does not work:
Record 1, WeekStart = 10/1/16
Record 2, WeekStart = 10/9/16

Link to comment
Share on other sites

If I understand this correctly, the WeekStart field should be a Date field, with auto-entered calculated value =

Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 1

This will automatically enter the date of the Sunday of the current week (i.e. the week in which the record was created).

To fix existing records, replace the field's contents with a similar calculation, using WeekStart instead of Get ( CurrentDate ). Make sure you have a backup before you do this, as there is no undo.

 

28 minutes ago, RyanESmith7 said:

I'd like to find the previous WeeklyLog record that the client had

What you show is a relationship, not a find.

 

Edited by comment
Link to comment
Share on other sites

Yes, I am looking for a relationship, not a find. My apologies for the confusion.

I can't use the first day of the week because the client can start their tracking on any day of the week they want AND they can even skip weeks of tracking on occasion. So I'm not actually looking for Sunday, I want the record (from the same table, via table occurrence) that is just before this record. 

For example, I would want it to return something like this:

3dfb524b804e4ef8a8982413ef3bbcb5.png

My current calculation is correct except for that RED one in the example above. Because the user skipped 15 days between logging. So I'm trying to figure out the WeekStart of the record before.

Thanks for spending so much time trying to understand me... sometimes it's hard for me to convey my issues in writing.

Link to comment
Share on other sites

10 minutes ago, RyanESmith7 said:

I want the record (from the same table, via table occurrence) that is just before this record. 

Then you should define the relationship as:

WeeklyLog::WeekStart > PrevWeekLog::WeekStart

and sort the records on the PrevWeekLog side by WeekStart, descending. This will make the most recent previous log the first related record of each log. No calculation fields are required for this.

Link to comment
Share on other sites

  • 3 weeks later...

This topic is 2726 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.