topic Re: Creating Average for Last 24 Months Based on Date Selected in QlikView

Creating Average for Last 24 Months Based on Date Selected

Wed, 12 Aug 2015 17:10:41 GMT

Hi all,

I need to calculate average of past 24 months based on Date Selected.

well the scenario is if the selected date is 12/Aug/2015 then i need to get all the values between 12/Aug/2015 and 12/Aug/2012 and Averaged those 24 Months Data.

Please Help me with this.

Thanks.

MRKachhiaIMP‌dvqlikview‌gwassenaar‌celambarasan‌tresesco‌mby‌swuehl‌jagan‌Qlik Community‌Scripting‌App Development‌

Re: Creating Average for Last 24 Months Based on Date Selected

maximiliano_vel — Wed, 12 Aug 2015 17:50:20 GMT

Try

Avg({<Date={">= $(=AddMonths(Max(Date),-24) ) <=$(=Max(Date))"}>} value)

Re: Creating Average for Last 24 Months Based on Date Selected

MK_QSL — Wed, 12 Aug 2015 17:54:01 GMT

EDITED:

Are you looking for 36 Months data or 24 Months?

Use something as below for 24 Months

SUM({<Date = {">=$(=Date(AddMonths(Max(Date),-24)))<=$(=Date(Max(Date)))"}>}Sales)

Re: Creating Average for Last 24 Months Based on Date Selected

CELAMBARASAN — Wed, 12 Aug 2015 17:54:38 GMT

Try like this

Sum({<DateField={">=$(=AddMonths(Max(DateField), -24))<=$(=Max(DateField))"}>} Sales)/24

Celambarasan

Re: Creating Average for Last 24 Months Based on Date Selected

sasiparupudi1 — Wed, 12 Aug 2015 19:24:29 GMT

avg{<YourDate = {">=$(=Date(AddMonths(Max(YourDate),-24))) <=$(=Date(Max(YourDate)))"}>}YourValue)

Re: Creating Average for Last 24 Months Based on Date Selected

jagan — Thu, 13 Aug 2015 02:26:18 GMT

If it is 12/Aug/2015 and 12/Aug/2012

Sum({<YearFieldName=, QuarterFieldName=, MonthFieldName=, DateFieldName={">=$(=AddMonths(Max(DateFieldName), -36))<=$(=Date(Max(DateFieldName)))"}>} Sales)/36

If it last 24 months then use

Sum({<YearFieldName=, QuarterFieldName=, MonthFieldName=, DateFieldName={">=$(=AddMonths(Max(DateFieldName), -24))<=$(=Max(DateFieldName))"}>} Sales)/24

Hopw this helps you.

Regards,

Jagan.

Re: Creating Average for Last 24 Months Based on Date Selected

svenkita — Thu, 13 Aug 2015 03:47:30 GMT

Attached is a sample hop this helps