Average by year?

[#1]

Hi guys,

I'm fairly new to Qlikview so I'm still learning.

What I want to do is that I want a listbox that displays the Average Delivery Days, it's looking something like this at the moment:

Average Delivery Days
Total	8,7468
2011
2012
2013

As you can see I've already got the Total "Avg(AVERAGE_DELIVERY_DAYS)", so what I'm wondering is how you get the average for each specific year?

Jonathan                                                                                                                                          

[datanibbler]

Hi Jonathan,

welcome. We are all learning here, even the "oldest" members, so don't worry.

There is a fct. "NETWORKDAYS" (think of it as net_workdays ;-). You can pass it the YEARSTART() and YEAREND() as parameters and it will return you an exact nr. (no average) of workdays (Mo-Fri, no holidays accounted for asaIk, you'd have to subtract those) of a specific year.

HTH

Best regards,

DataNibbler

[nizamsha]

PFA thats the statistics box

[jonathandienst]

Hi

This looks like a data model problem. I suspect that the year value associated with the average delivery days field is null, so the total works, but not the individual years.

I suggest that you post a sample of your model to diagnose this in more detail.

HTH

Jonathan

[#5]

The table I made in the post is how I want it to look. I think my problem is that I have PERIOD wich contains:

2011-2012
2012-01, 2012-02, 2012-03, 2012-04, 2012-05, 2012-06, 2012-07, 2012-08, 2012-09, 2012-10, 2012-11, 2012-12

2013-01, 2013-02, 2013-03, 2013-04, 2013-05, 2013-06, 2013-07, 2013-08, 2013-09, 2013-10, 2013-11, 2013-12

Is there anyway to cut these down to 2011, 2012 and 2013. Instead of monthly?

[jonathandienst]

Jonathan

In your load script, add this to LOAD statement that loads PERIOD

LOAD ....

     PERIOD,

     Num(SubField(PERIOD), '-', 1)) As YEAR,

     Month(Date#(PERIOD, 'YYYY-MM')) As MONTH,

     ...

HTH

Jonathan

[vipin_mishra479 · Accepted solution]

Hi,

Use

=sum(aggr(Amount,Year))

Hope this will help u...||

[#8]

Okay, I see. So if I want my table to look like that, I have to modify the script. I'll have to see how this ends

Thank you

[#9]

Hi,

and thank you. This worked! I went for a Listbox like this:

Field: PERIOD
Expression: =Sum(Aggr(AVERAGE_DELIVERY_DAYS,PERIOD))

My only problem now is that PERIOD displays YYYY-MM so I get:           

2012-01                    4,81
2012-02                    5,71

2012-03                    6,48

and so on.. Id like to just have it something like this:

Overall                      6,34

2011                         5,67

2012                         6,58

2013                         7,19

But I guess it has to do with how the script is made, just like Jonathan Dienst wrote.

Thank you.