Flag last Monday to Friday

[#1]

Hi,

My Dashboard reloads at 7am every day so the the dashboard has data upto midnight of the day before.  What I would like to do is when the dashboard reloads on a Monday morning is to flag the last working week (Mon-Fri).

Can anyone help with this?  My date field is CalDate.

Thanks,

Tim

[sunny_talwar]

Only for Monday Morning you want to flag the last week? If it's Tuesday, no flags at all?

[migueldelval]

Hi Tim,

Normally it´s better to use a Master calendar to manage all the Dates. Here you have enough information:

Calendars

But Maybe you can use a conditional clause "If(weekday(CalDate)='mon', date((CalDate)-3), CalDate)"

Regards

Miguel del Valle

[#4]

Hi,

Thanks for your replies and sorry for the delay in replying.

What I need to do is each Monday, Tuesday and Wednesday create the same report as our data is updated.

The report needs to cover the last two full weeks from Monday to Sunday, eg for today's report (12/12/2016) it would cover Monday 28th Nov to Sunday 11th Dec. 

Also I need to include the same "Previous Full Week for Last Year" as a comparison which in this case would be Monday 5th December 2015 - Sunday 11th December 2015.

At the moment I am using the following to get a rolling previous 14 days and the "Last Week - Last Year" Flag,  this works in principal for on a Monday Morning, but doesn't fit when the  report is re-run on a Tuesday or Wednesday.

if (date(date) >= Date(Today()-14) and date(date) < today(), 1) as Last2WeeksFlag,

if (date(date) >= date(Addmonths(Date(Today()-7),-12)) and  date(date) < date(Addmonths(Date(Today()),-12)) , 1) as LastWeekLastYearFlag,

Thanks again for any help.

[sunny_talwar]

For this year's flag you can try this:

If(Date >= WeekStart(Today(), -2) and Date <= WeekEnd(Today(), -1), 1, 0) as Flag;

Note: Make sure your environmental variable for FirstWeekDay is set to 0

SET FirstWeekDay=0;

For last year, you said Dec5th to Dec 11th and you mentioned Dec 11th 2015 to be a Sunday? It was a Friday on Dec 11th 2015. I am not sure what range you are looking to get for last year. May be if you can provide more details, we might be able to help you better