when an year is having 52 weeks or leap year 53 weeks

[Anonymous]

Hi Guru's,

RangeMax (
avg( aggr( sum({<Year = {$(=vCurrentYear)},Week ={">= $(=vCurrentWeek-4)<= $(=vCurrentWeek-1)"}>} TRX_NET_SALES), WeekStart,ITEM_SKU)),

Avg (
aggr(
sum({<Year = {$(=vCurrentYear-1)} ,
Week ={"<= $(=vCurrentWeek+4)>= $(=vCurrentWeek+1)"}>} TRX_NET_SALES),WeekStart, ITEM_SKU)),


Fractile( aggr( sum({<Year = {$(=vCurrentYear)},Week ={">= $(=vCurrentWeek-8)<= $(=vCurrentWeek-1)"}>} TRX_NET_SALES), WeekStart,ITEM_SKU),0.75),


Fractile(aggr(
sum({<Year = {$(=vCurrentYear-1)} ,
Week ={"<= $(=vCurrentWeek+8)>= $(=vCurrentWeek+1)"}>} TRX_NET_SALES),WeekStart, ITEM_SKU),0.75)

)

Issue: the above condition works fine until current week is not one what if its one then it will return -3, 0

So we have to write an expression where it will work fine even though the current week is one and also it should work well even wen an year is having 52 weeks or leap year 53 weeks, so please write an expression accordingly.

example:if we are in 2016 first week it should also calculate 52,51,50,49 like that

can you please help me on this

[jjbom1990]

What you could do is the use of a WeekYearID that way you can just substract 4 and get the previous 4 id's which could be in an other year.

autonumber(Year(TempDate)&Week(TempDate)) as WeekYearID

Is how you would make a WeekYearID in your script. It is very easy to work with this in any calculation.

Jasper

[#3]

Hi Devendar,

Create a field in the script with Year&Week number using auto number function  then modify your expression accordingly.

Regards

John

[Anonymous]

hi john,

can i create one more field in the script year & week as week year ??

how can i modify above expression ?

[jjbom1990]

RangeMax (
avg( aggr( sum({<WeekYearID={">= $(=WeekYearID-4)<= $(=WeekYearID-1)"}>} TRX_NET_SALES), WeekStart,ITEM_SKU)),

Avg (
aggr(
sum({<
WeekYearID={"<= $(=WeekYearID+4)>= $(=WeekYearID+1)"}>} TRX_NET_SALES),WeekStart,ITEM_SKU)),


Fractile( aggr( sum({<WeekYearID={">= $(=WeekYearID-8)<= $(=WeekYearID-1)"}>} TRX_NET_SALES), WeekStart,ITEM_SKU),0.75),


Fractile(aggr(
sum({<WeekYearID={"<= $(=WeekYearID+8)>= $(=WeekYearID+1)"}>} TRX_NET_SALES),WeekStart,ITEM_SKU),0.75)

I believe that should do the trick, see if it matches your desired outcome.

[Anonymous]

how can we recognize whether it is a current year or previous year ??

for that we need to add vcurrentyear and vcurrentyear-1 in the expression right

how it will come with only weekyearid??

[jjbom1990]

‌the weekyearid has the year inside it. Weekyearid (when yo ustart at januari 2010, is on december 52, januari 2011 has weekyear id 53.

so when you are in week 1 in 2011, it has an ID of 53, when you substract 4 you get ID 49 which is week 48 in 2010. Can you follow?