Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Average of all preceding values

Hey!!

I would like to plot the following expression:

z_i = \lambda \bar x_i + \left ( 1 - \lambda \right)z_{i - 1}.

For that I need the average of all preceding values. My dimension is a column with dates (days).

I tried: lambda*avg(L_Z1V1)+(1-lambda)*avg({1-$}L_Z1V1)

But it didn't work.

Thank you very much!!!

Eduardo

11 Replies
whiteline
Honored Contributor II

Re: Average of all preceding values

Hi.

To write iterative expressions you should use inter-record functions like above() or before() depending on the table structure.

Set analysis ({1-$}) that you've used operates with sets of data.

{1-$} means just 'all data excluded by user selection'.

Not applicable

Re: Average of all preceding values

Thank for your help! But I don't understand how to use above and before in this case. In my chart I want a value for each day. So for each day I need the average of L_Z1V1 (avg(L_Z1V1)) and the average of all preceding days... How can I calculate this??

Thank you very much!!!

Eduardo

Not applicable

Re: Average of all preceding values

Thank for your help! But I don't understand how to use above and before in this case. In my chart I want a value for each day. So for each day I need the average of L_Z1V1 (avg(L_Z1V1)) and the average of all preceding days... How can I calculate this??

Thank you very much!!!

Eduardo

whiteline
Honored Contributor II

Re: Average of all preceding values

Hi.

You could try something like this (similar as manual accumulation).

=rangeavg(above(Sum(Value), 0, rowno()))

Not applicable

Re: Average of all preceding values

Hey!

Thank you for your help! But I still have some problems... In my chart I use as dimension: "days". The thing is that I have more than one value per day. So my rangeavg(...) should take all the values preceding the day in which I am calculating.

DatumL_Z1V1
2012.01.03 16:03:550,41
2012.01.03 20:31:430,434
2012.01.03 23:13:580,42
2012.01.04 13:56:060,431
2012.01.04 16:21:060,455
2012.01.04 22:14:280,45
2012.01.04 22:20:310,437
2012.01.04 22:26:450,448
2012.01.04 22:35:220,437
2012.01.04 23:16:090,449
2012.01.11 10:10:070,449
2012.01.11 10:37:040,422

For the first value rangavg should deliver 0. For the second value, rangavg should deliver the average value of all the values of 2012.01.03. I dont konw if I am explaining properly my doubt...

Thank you for your help!!

Eduardo

whiteline
Honored Contributor II

Re: Average of all preceding values

In this case you could use aggr() function.

But it uses loading sort-order.

You could perform the calculation in script.

Not applicable

Re: Average of all preceding values

At the beginning I wanted to use aggr functions. I tried 1-$ but it is not what I need... I runned out of ideas..

Thank you for your effort!!

Eduardo

whiteline
Honored Contributor II

Re: Average of all preceding values

There is aggr() function that you could use to iterate over all your values during a day.

While at the begining you've tried aggregation functions (avg) with set analysis expression.

Not applicable

Re: Average of all preceding values

Thank you very much for your time but I still don't know how to express that I want the average of the previous days...

Thank you!

Eduardo

Community Browser