Select every first Monday of a week for the past Month

[terezagr]

Hi all,

I have been struggling with this problem for quite a few days already.

The scenario is as follow:

Snapshots are taken twice a day (8am and 6pm) every day except Sunday. I am trying to find a way how to select every Monday's 8am snapshot for the past month and then compare it to the past year Monday's snapshots…So lets just say: today is Monday 27th January, so I would like to have pre-selected:

Monday 27th January 2014 8am snapshot

Monday 20th January 2014 8am snapshot

Monday 13th January 2014 8am snapshot

Monday 6th January 2014 8am snapshot

So far I managed do this for the most current Monday:

Within an app there is a button that has action assign as follow:

Select Field-Field:Snapshot, Search String: Max(WeekStart(snapshot), 'DD/MM/YYYY hh:mm:ss')  

State: Date 1

This will select the most current Monday's snapshots.

Problem: How to pre-select all the past 4 Mondays 8am snapshots?

Also, as I want to compare the last year January Monday's snapshots, how do I again select the past year Mondays 8 am snapshots? I have to also consider the fact that for last year the snapshots does not exist for the same dates as last year  27th, 20th, 13th and 6th were Sundays…so I have to take the ones that were created on Saturday or Friday. I was thinking about IF statement where:

if (date(addYears(max(Snapshot),-1),'DD/MM/YYYY'),date(addYears(max(WeekStart({[Date 1]}Snapshot),-1)),'DD/MM/YYYY'), date(addYears(max(WeekEnd({[Date 1]}Snapshot),-1)),'DD/MM/YYYY'))

Anyone has any solution or advice?

Thanks in advance!

T.

[rustyfishbones]

Do you have a field called WeekNumber, Month, Date,Year and Weekday? in your Calendar Script

If so there will be no need for the Expression

[terezagr]

I have field date, week, month and weekStart, no problem to add the rest of it. What would you do with those to solve the problem??

[rustyfishbones · Accepted solution]

You can select WeekDay as Monday and the Month you want from the list Boxes

See below result

[rustyfishbones]

to add a button use Select in Field and Toggle Select

So Select in Field WeekDay >> Search String Mon

and then Toggle Select  YearMonth >> Search String Max(YearMonth)