Current week Vs Last Year current week data, Depending on reload date

[nihalbuddy09]

Hi all,

We have a pivot table where we are displaying the data for current week and last year same week. Below are the two expressions we are using it on the report currently.

1. For current week data:
=Sum({<CalendarDate={'>=$(vWeekStart)<=$(vWeekEnd)'}>}(UnitVolume))
by this expression we are displaying the data for the current week, for example week started on Monday 28 Jan'19 and if we run the report  on 31st Jan , we will get the data for 28, 29 and 30th. 

2. For Last year same week data:
=Sum({<CalendarDate={'>=$(vLYWeekStart)<=$(vLYWeekEnd)'}>}(UnitVolume))

by this expression we are showing the data for last year same week i.e, from 29/01/2018 to 04/02/2018 (total 7 days of data is displaying). But, we got the change in requirement and here when we ran the report on the 31st Jan'19 we should get the data for 3 days only  i.e for 29, 30 and 31st (like we got for the current year- current week data) instead of displaying the 7 days data. Later next day when we will run the report, it should display 4 days of data...

Please help me on this so that I can correct the last year set analysis expression.

I am searching here in our community was unable to find the thread which relate to my scenario, hope I explained it correctly, please advise.

Thanks,

Nihal

[CarlosAMonroy]

Hi Nihal,

It does make sense. You just have to take your current variables that have data range for current year, and put last year's year.

like:

vLYWeekStart:

makedate(year($(vWeekStart)-1),month($(vWeekStart)),day($(vWeekStart))) 

same for the other variable.

Hope that helps.

Carlos M

[nihalbuddy09]

Hi Carlos,

Thank you for your response and these are the variables which we are using it currently to display the data.

Let vDateofExtractOffset=0;
Let vDateofExtract=date(Today()-$(vDateofExtractOffset));
Let vWeekStart = WeekStart('$(vDateofExtract)',0,0);
Let vWeekEnd = WeekEnd('$(vDateofExtract)',0,0);
Let vYearStart = '01/03/2018';
Let vLYWeekStart = WeekStart('$(vDateofExtract)',-52,0);
Let vLYWeekEnd = WeekEnd('$(vDateofExtract)',-52,0);

As it looks like your suggested approach of creating a variable for Last year is also same as what we are using it currently.
Still I am getting the 7 days of data for last year same week, instead of restricting the data.

Please advise how to correct my expression. 

Thanks,

Nihal

[nihalbuddy09]

Hi All,

I am getting number of examples in our community related to display Last year's same week data, but unable to find the one for my scenario. 

Someone please advise how can I achieve my scenario. 

Thanks,

Nihal 

[CarlosAMonroy]

You have to change the last year variables to be:

Let vLYWeekStart = makedate(year($(vWeekStart)-1),month($(vWeekStart)),day($(vWeekStart)));
Let vLYWeekEnd = makedate(year($(vWeekEnd)-1),month($(vWeekEnd)),day($(vWeekEnd)));

Thanks,

Carlos

[nihalbuddy09 · Accepted solution]

Hi All,

We have achieved the required results by creating two more variables and modifying the LY's expression. 

Here are the additional variables and Last year same week expression which we have used. Hope it will be useful to someone at sometime 🙂 

let vDaystoAddforLYThisWeek = pick( wildmatch(weekday(today(2)),'*Mon*','*Tue*','*Wed*','*Thu*','*Fri*','*Sat*','*Sun*'),0,1,2,3,4,5,6);

Let vLYWeekEndDaysAdded = Date('$(vLYWeekStart)'+$(vDaystoAddforLYThisWeek));

=Sum({<CalendarDate={'>=$(vLYWeekStart)<=$(vLYWeekEndDaysAdded)'}>}(UnitVolume))

Thank you Carlos for looking on to this. 

Nihal