topic Re: How to calculate total amount of last 6 weeks in QlikView

How to calculate total amount of last 6 weeks

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to calculate total amount of last 6 weeks

ramasaisaksoft — Tue, 31 May 2016 12:28:10 GMT

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])

Re: How to calculate total amount of last 6 weeks

sunny_talwar — Tue, 31 May 2016 13:45:40 GMT

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

Re: How to calculate total amount of last 6 weeks

Wed, 01 Jun 2016 08:38:04 GMT

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

Re: How to calculate total amount of last 6 weeks

sunny_talwar — Wed, 01 Jun 2016 11:10:28 GMT

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)

Re: How to calculate total amount of last 6 weeks

sunny_talwar — Wed, 01 Jun 2016 11:11:11 GMT

Or this:

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

Re: How to calculate total amount of last 6 weeks

Thu, 02 Jun 2016 07:43:41 GMT

Hi Sunny,

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

Regards,

Jessica

Re: How to calculate total amount of last 6 weeks

Thu, 02 Jun 2016 07:50:56 GMT

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,

selected date=2016.Jan.3

then

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

selected date=2016.Nov.5

then

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

Regards,

Jessica

Re: How to calculate total amount of last 6 weeks

sunny_talwar — Thu, 02 Jun 2016 12:24:41 GMT

Is this year to date based on your fiscal year?