How to calculate total amount of last 6 weeks

[#1]

Hello,

I want to calculate the total amount of last 6 weeks based on selected date, that means that the total amount of last 6 weeks is always be changed when i selected different date.

Thanks

Jessica

Message was edited by: Jessica lv attached pls find the test.qvw

[ramasaisaksoft]

Hi Jessical,

=sum({1<[WADAT_IST_Actual Goods Movement Date] = {">=$(=weekStart(Max([WADAT_IST_Actual Goods Movement Date]),-6))<=$(=weekEnd(Max([WADAT_IST_Actual Goods Movement Date])))"},
region={$(vRegion)},Country={$(vMarket)},Global_Brand_Name={$(vBrand)},Therapy={$(vTherapy)},Local_Product_Description={$(vProduct)}>}OTIFCounter)/
sum({1<[WADAT_IST_Actual Goods Movement Date] = {">=$(=WeekStart(Max([WADAT_IST_Actual Goods Movement Date]),-6))<=$(=WeekEnd(Max([WADAT_IST_Actual Goods Movement Date])))"},
region={$(vRegion)},Country={$(vMarket)},Global_Brand_Name={$(vBrand)},Therapy={$(vTherapy)},Local_Product_Description={$(vProduct)}>}[Lines Ordered])

[sunny_talwar]

May be this:

Sum({<DateField = {"$(='>=' & Date(Max(DateField) - 42, 'DateFieldFormatHere') & '<=' & Date(Max(DateField), 'DateFieldFormatHere'))"}, Month, Year, MonthYear, Quarter, QuarterYear, Week, WeekYear>}Sales)

Where these are all the date related fields where you might make selections -> , Month, Year, MonthYear, Quarter, QuarterYear, Week, WeekYear

[#4]

Hi Sunny, it has some proble, i didn't get result, pls see attachment. thanks, Jessica.

[sunny_talwar]

I think you were supplying the incorrect date format to your set analysis expression. For the sample you can try this:

=Sum({<Date = {"$(='>=' & Date(Max(Date) - 3) & '<=' & Date(Max(Date)))"},[Month(Date)],[Year(Date)],[Week(Date)] >}Value)

[sunny_talwar]

Or this:

=Sum({<Date = {"$(='>=' & Date(Max(Date) - 3, 'M/D/YYYY') & '<=' & Date(Max(Date), 'M/D/YYYY'))"},[Month(Date)],[Year(Date)],[Week(Date)] >}Value)

[#7]

Hi Sunny,

I re-checked, your formula is correct, thanks.

Regards,

Jessica

[#8]

Hi Sunny,

If i want to sum the value between selected to last 1 Oct (maybe in same year, maybe in last year), then how to work out formula ?Thanks.

For example,

if

selected date=2016.Jan.3

then

Vaule sum = start 2015.Oct.1 to 2016.Jan.3

if

selected date=2016.Nov.5

then

Vaule sum = start 2016.Oct.1 to 2016.Nov.5

Regards,

Jessica

[sunny_talwar]

Is this year to date based on your fiscal year?