topic Re: Rolling 6 months in QlikView

Rolling 6 months

Wed, 23 Oct 2013 07:07:35 GMT

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

Re: Rolling 6 months

thanstad — Wed, 23 Oct 2013 07:19:59 GMT

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

Re: Rolling 6 months

er_mohit — Wed, 23 Oct 2013 07:52:33 GMT

Try this

rangeavg(above(avg(aggr(Inventory,Period,Year)),0,6))

Re: Rolling 6 months

Gysbert_Wassenaar — Wed, 23 Oct 2013 08:41:36 GMT

This document may help you find which solution works best for you: Calculating rolling n-period totals, averages or other aggregations

Re: Rolling 6 months

Thu, 24 Oct 2013 07:05:04 GMT

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?

Re: Rolling 6 months

Thu, 24 Oct 2013 07:13:28 GMT

Try This

avg({<Date={'>=$(Vrolling6)<=$(vMaxDate)'}>}Monthendvalue)

Vrolling6=Date(MonthStart(vMaxDate,-5),'DD/MM/YYYY')

Vmaxdate=Date(max(Date),'DD/MM/YYYY')

Re: Rolling 6 months

thanstad — Thu, 24 Oct 2013 09:03:30 GMT

Hello Janne, I tried based on your data and it seems to work here by my.

Re: Rolling 6 months

Thu, 24 Oct 2013 09:08:48 GMT

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