Creating Average for Last 24 Months Based on Date Selected

[#1]

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‌

[maximiliano_vel · Accepted solution]

Try

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

[MK_QSL]

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)

[CELAMBARASAN]

Try like this

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

Celambarasan

[sasiparupudi1]

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

[jagan]

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.

[svenkita]

Attached is a sample hop this helps