QlikView App Dev

[srikanth12345]

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.

[felipedl]

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.

[srikanth12345]

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 · Accepted solution]

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.

[srikanth12345]

Thanks a lot

[felipedl]

No problem, glad it helped .