Cumulative sum for calculate the average

Tue, 24 Feb 2015 01:37:40 GMT

Hi, community.

I'm having trouble solving a problem and hope someone can lend me a hand.

There is an inventory table in wich I have:

line_id: is the dimension for the graphic.
ini_inv: initial existence field (I only have the initial existence for the 1st day of the month).
ent_inv: inventory entries field.
sal_inv: inventory outputs field.
date: is the date of the inventory.

With those fields I can calculate the final existence of a day: (ini_inv + ent_inv - sal_inv).

As I only have the 'ini_inv' for the 1st day of the month, the final existence of each day is equivalent to the 'ini_inv' of

the next day and so on.

i.e. If I select:

Year=2015

Month=02

Day=03

My expression for calculating the final existence of the selected day is:

Sum({$<Day = {"<=$(=Day(Max(Date)))"}>} ini_inv + ent_inv - sal_inv)

So far so good, the problem is to calculate the average existence for which I must sum the final existence of every day, from day 1 to selected day (in this case 3) and divide it by the number of the selected day. That is the sum of the above expression for each day from 1 to 3 divided by 3. Something like this:

(
Sum({$<Day = {1}>} ini_inv + ent_inv - sal_inv) +   // Final existence of day 1 = 'ini_inv' of day 2.

(Sum({$<Day = {1}>} ini_inv + ent_inv - sal_inv) +
  Sum({$<Day = {2}>} ent_inv - sal_inv)) +           // Final existence of day 2 = 'ini_inv' of day 3

((Sum({$<Day = {1}>} ini_inv + ent_inv - sal_inv) +
  Sum({$<Day = {2}>} ent_inv - sal_inv)) +
Sum({$<Day = {3}>} ent_inv - sal_inv))              // Final existence of day 3.
) / 3

Is it possible to achieve this with an expression?

Thanks in advance.

Rubén.

topic Cumulative sum for calculate the average in QlikView

Cumulative sum for calculate the average