Customized Variable

[rupaliqlik]

Hi Experts,

I have on scenario where I developed on dashboard which is monthly.So when I reload in this month It should show Previous month Data.In my case i want to create one variable.Mu user requirement is like this.In previous month exclude all sunday. Then from month end to minus 10 days .It should fall in any day .Then that days nearest Monday should peek.If 10th day is fall on Monday so it means 10 days are not complete so We should take prior Monday date.

For Eg:-

In september 2017 Month end is 30th september. After Exclude Sunday My 10th day would be 19th Sep and it was Tuesday.So should peek nearest Monday and that will be 18th Sep.But In August My 10th day falls on Monday which 21st august So in this case i should take previous monday date 14th august.I need some idea to handle this.Below logic is failed for June month

vDate=if(weekday(date(Monthend(addmonths(Today(),-2))-11))<>'Mon',Weekstart(date(Monthend(addmonths(Today(),-2))-11)),date(date(Monthend(addmonths(Today(),-2))-11)-7))

Regards,

Rupali Ethape

[swuehl]

Your formula seems to work for me if you don't count the monthend date for the 10 days. Otherwise change your -11 to -10.

[swuehl]

You can also rewrite your expression like

=If(WeekDay(Monthend(today(),-4))='Fri',WeekStart(Monthend(today(),-4),-2),WeekStart(Monthend(today(),-4),-1))

Just check if monthend falls on a Friday (again, you need to check if 10 days period includes or excludes the monthend, just adapt the weekday to check, it must be constant for your requirement). Then you can branch into calculating the weekstart 2 weeks or 1 week before monthend.