topic Re: Average between the dates in QlikView

Average between the dates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Thanks in advance.

Re: Average between the dates

felipedl — Wed, 29 Nov 2017 17:47:21 GMT

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.

Re: Average between the dates

Anonymous — Thu, 30 Nov 2017 06:24:11 GMT

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

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

Re: Average between the dates

felipedl — Thu, 30 Nov 2017 12:36:38 GMT

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.

Re: Average between the dates

Anonymous — Fri, 01 Dec 2017 03:44:26 GMT

Thanks a lot

Re: Average between the dates

felipedl — Fri, 01 Dec 2017 12:54:08 GMT

No problem, glad it helped .