Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
PFA thats the statistics box
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
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?
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
Hi,
Use
=sum(aggr(Amount,Year))
Hope this will help u...||
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
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.