Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

Thanks in advance.

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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".

Sample.png

Felipe.

View solution in original post

5 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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:

Sample.png

Felipe.

Anonymous
Not applicable
Author

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


felipedl
Partner - Specialist III
Partner - Specialist III

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".

Sample.png

Felipe.

Anonymous
Not applicable
Author

Thanks a lot

felipedl
Partner - Specialist III
Partner - Specialist III

No problem, glad it helped .