Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am preparing inventory turnover report and I would like to add there a calculation that shows moving 6 month average
I know this is simple question for most of you, but I am still quite rookie with Qlikview, so I would highly appreciate your help
To clarify my issue, I have attached example below. In my data, there is year, period, inventory and monthly usage information. What I would like to come up with is these two six months rolling average columns (Inventory (six months avg) 1037 is the average of inventories in periods 2011/1-2011/6)
Year | Period | Inventory | Usage | Inventory (rolling six month avg) | Usage (rolling six month avg) |
2011 | 1 | 1000 | -100 | ||
2011 | 2 | 1100 | -90 | ||
2011 | 3 | 1050 | -110 | ||
2011 | 4 | 1000 | -105 | ||
2011 | 5 | 990 | -110 | ||
2011 | 6 | 1080 | -70 | 1037 | -98 |
2011 | 7 | 1050 | -100 | 1045 | -98 |
2011 | 8 | 1010 | -90 | 1030 | -98 |
2011 | 9 | 910 | -150 | 1007 | -104 |
2011 | 10 | 950 | -150 | 998 | -112 |
2011 | 11 | 960 | -100 | 993 | -110 |
2011 | 12 | 1000 | -90 | 980 | -113 |
2012 | 1 | 1000 | -100 | 972 | -113 |
2012 | 2 | 1100 | -90 | 987 | -113 |
2012 | 3 | 1050 | -110 | 1010 | -107 |
2012 | 4 | 1000 | -105 | 1018 | -99 |
2012 | 5 | 990 | -110 | 1023 | -101 |
2012 | 6 | 1080 | -70 | 1037 | -98 |
2012 | 7 | 1050 | -100 | 1045 | -98 |
2012 | 8 | 1010 | -90 | 1030 | -98 |
2012 | 9 | 910 | -150 | 1007 | -104 |
2012 | 10 | 950 | -150 | 998 | -112 |
2012 | 11 | 960 | -100 | 993 | -110 |
2012 | 12 | 1000 | -90 | 980 | -113 |
I think this would be easiest to do in expression instead of script?
Thanks
Regards
Janne
Try this,
=rangeavg(above(avg(field),0,6)) you can use Above for calculating on previous observations. You need of course in this example to group your data by month. 0 is the starting line and 6 is the number of observation above this. If you put this into at linechart with a spline option it will be nice. Check out the help on range - there are a lot of possibilities on this functions.
Good luck!
Tormod Hanstad
Try this
rangeavg(above(avg(aggr(Inventory,Period,Year)),0,6))
This document may help you find which solution works best for you: Calculating rolling n-period totals, averages or other aggregations
Thanks for your hints! But I am struggling to utilize them. I have now the table below. This view is for one material and I would like to see in the last column the average of the current and previous period. For some strange reason the rangeavg formula there doesn't do the job. Is it somehow wrong or do I have some kind of problem with my data?
Try This
avg({<Date={'>=$(Vrolling6)<=$(vMaxDate)'}>}Monthendvalue)
Vrolling6=Date(MonthStart(vMaxDate,-5),'DD/MM/YYYY')
Vmaxdate=Date(max(Date),'DD/MM/YYYY')
Hello Janne, I tried based on your data and it seems to work here by my.
Ok, I was thinking also that this should work, but it doesn't for me. Would you mind sending your qvw file that I could see in where I am doing this wrong?
Thanks!
Janne