topic Re: Chart with a moving average in QlikView

Chart with a moving average

Tue, 22 Oct 2013 14:47:43 GMT

Hey everyone,

I woule like to create an chart that is showing the moving average of completness (in %) of my task, and i don't find the expression needed to use a moving average.

For exemple of moving average in my source file, i have:

Date	# tasks completed / month	# total tasks / month	% Completness	Moving average
1/1/2013	4	5	80%	80%
1/2/2013	3	4	75%	77,5%
1/3/2013	6	10	60%	71,67%
1/4/2013	12	15	80%	73,75%
1/5/2013	2	2	100%	79%

In this way, i would like to have a line chart showing the moving average evolution by date.

I don't know if it's clear, but it will be really nice if someone can help me on this tricky chart

Best regards,

Arnaud

Re: Chart with a moving average

Tue, 22 Oct 2013 15:10:34 GMT

Hi,

See the attached app,

Re: Chart with a moving average

Tue, 22 Oct 2013 15:35:07 GMT

Dear Marie-Sophie,

Thank you for your helpfull example, i think i understand the formula.

How would you implement this in a chart where the Dimension chosen is the date, and my current expression is:

"Count(Distinct if(Action_Date >= Action_Date_Close,ID_Planning)) / Count(DISTINCT ID_Planning)"

ID_planning is an unique code for every task.

Thank you so much in advance, i'm pretty new on Qlikview, and it's a bit tricky at the start

Best regards,

Arnaud

Re: Chart with a moving average

Tue, 22 Oct 2013 15:41:48 GMT

Could you post your qvw (or part of it, or even an application with false date ..) ?

It would be then easier to help you.

Re: Re: Chart with a moving average

Thu, 24 Oct 2013 09:14:21 GMT

Dear Marie-Sophie,

Here is the Qlikview with an example of the graphic where i want to change the expression, in a moving average expression.

Best regards,

Arnaud

Re: Re: Re: Chart with a moving average

Thu, 24 Oct 2013 09:42:44 GMT

Hi,

I cannot upload files right now but you can maybe create a second expression with this formula :

Rangesum(Top([% of Recurrent tasks Completed], 1, RowNo()))/ rowno()

Then you unchek line and symbol for your first expression and check it for the new one.

The only problem with this formula is that lines removed by your if statement are counted in the moving average.

I'll try to find a better solution.

Re: Re: Re: Chart with a moving average

Thu, 24 Oct 2013 12:25:04 GMT

Thank you a lot for your time !