Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative sum for calculate the average

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.

0 Replies