# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Highlighted
New Contributor II

## Average between the dates

Hi Everyone,

I have a requirement to display average value between all the date periods.Two below steps we should consider for calculating average value.

1.I want to consider the value based on latest date on each date period.

example:

For Id 100 ,Company ID A01 and Date period 2017 the value is 500 (3/1/2017 latest date)

Id 100 ,Company ID A01 and Date period 2016 the value is 500 (3/1/2017 latest date)

Id 100 ,Company ID A01 and Date period 2015 the value is 500 (3/1/2017 latest date)

2.Then i want to calculate the  average value i.e  average  value between these three date periods.

example:For Id 100  and Company ID A01  => (500+500+500)/3

Please find the attached sample data.

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Partner

## Re: Average between the dates

Kavya,

Attached a new QVW with what you need, basically the added code gets the combinations of ID, [Company ID] and [Date Period] where the flag is set to 1 (one), add a row count for each year and gets the last 3 occurences based on the difference of the Count and max Count for each row, so that it's possible to get only the 3 last of them (see ID 102 for example below) on the column "Count".

Felipe.

5 Replies
Highlighted
Partner

## Re: Average between the dates

Hi kavya,

I made a simple qvw with your data that gets what you want.

What I did was get the max latest date by ID,Company ID and Date Period so that I could get a flag for a set analysis purpose.

Attached a QVW for you to test and the script I used as a text file.

Hope it helps,

The output is the following:

Felipe.

Highlighted
New Contributor II

## Re: Average between the dates

Thanks for the reply.It is working fine.

However i need to consider latest  3 years dateperiod  data. Id 100 is  having data for 2015,2014 ,2013 and 2012.We need to consider only three years 2015,2014 ,2013

Example:1)Id:100 ,DatePeriod=2015, Latest date=3/1/2015,value 1000

Id:100, DatePeriod=2014, Latest date=3/1/2014 ,value 200

Id:100,DatePeriod=2013, Latest date=3/1/2013 ,value 1000

Id:100,DatePeriod=2012, Latest date=3/1/2012 ,value 500

2.

Example:Id:101 ,DatePeriod=2017, Latest date=3/1/2017,value 1000

Id:101, DatePeriod=2014, Latest date=3/1/2014 ,value 1000

Id:101, DatePeriod=2012, Latest date=3/1/2013 ,value 3000

Id:101, DatePeriod=2010, Latest date=3/1/2010 ,value 4000

Highlighted
Partner

## Re: Average between the dates

Kavya,

Attached a new QVW with what you need, basically the added code gets the combinations of ID, [Company ID] and [Date Period] where the flag is set to 1 (one), add a row count for each year and gets the last 3 occurences based on the difference of the Count and max Count for each row, so that it's possible to get only the 3 last of them (see ID 102 for example below) on the column "Count".

Felipe.

Highlighted
New Contributor II

Thanks a lot

Partner

## Re: Average between the dates

No problem, glad it helped .