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.
