Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
srikanth12345
Contributor II
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.

Thanks in advance.

1 Solution

Accepted Solutions
felipedl
Partner
Partner

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
Partner

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.

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

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

srikanth12345
Contributor II
Contributor II
Author

Thanks a lot

felipedl
Partner
Partner

No problem, glad it helped .