topic Re: Average by year? in QlikView

Average by year?

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Average by year?

datanibbler — Wed, 15 Jan 2014 08:28:22 GMT

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

Re: Average by year?

nizamsha — Wed, 15 Jan 2014 08:29:52 GMT

PFA thats the statistics box

Re: Average by year?

jonathandienst — Wed, 15 Jan 2014 08:47:52 GMT

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

Re: Average by year?

Wed, 15 Jan 2014 09:06:51 GMT

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?

Re: Average by year?

jonathandienst — Wed, 15 Jan 2014 09:57:35 GMT

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

Re: Average by year?

vipin_mishra479 — Wed, 15 Jan 2014 10:02:02 GMT

Hi,

Use

=sum(aggr(Amount,Year))

Hope this will help u...||

Re: Average by year?

Wed, 15 Jan 2014 10:42:54 GMT

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

Re: Average by year?

Wed, 15 Jan 2014 10:51:18 GMT

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.