Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Creating Average for Last 24 Months Based on Date Selected

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.

MRKachhiaIMPdvqlikviewgwassenaarcelambarasantresescombyswuehljaganQlik CommunityScriptingApp Development

1 Solution

Accepted Solutions
maximiliano_vel
Contributor III

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

Try

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

6 Replies
maximiliano_vel
Contributor III

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

Try

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

MVP
MVP

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

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

Try like this

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

Celambarasan

sasiparupudi1
Honored Contributor III

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

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

MVP & Luminary
MVP & Luminary

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

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
Contributor II

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

Attached is a sample hop this helps