Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate total amount of last 6 weeks

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

8 Replies
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

Not applicable
Author

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)

Not applicable
Author

Hi Sunny,

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

Regards,

Jessica

Not applicable
Author

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?