Problem with Sum

[#1]

I have a problem on my board the operation for sum not working:

Normally I leave QTY and I subtract every operation of movement of the result obtained previously.

Except here the problem is that it  takes me all the time the value of the original stock for the subtraction instead of the result obtained after the movements.

The normal results are  507 - 102 = 405 not 429

If anyone have a solution

[#2]

Not Working.

For explain i post an exemple;

i need to have this results from the expression :

for Code_Article D000140 normally the correct answer is 234 but i have -768

I think is the pivot table trouble my expression but i need the expression work up to the month

Thanks to the help

[p_verkooijen]

In a pivot use Before instead of Above

[#4]

1/ If i use this expression :

Sum(Qte_Stock_Inventaire)+ rangesum(Above(TOTAL Qte_Mvt , 0, RowNo()))

I found a good results when i select one "Code_Article" but for all "Code_Article" the results not good

2/ If i use this expression :

Sum(Qte_Stock_Inventaire)+  rangesum (Before(TOTAL Qte_Mvt , 0, RowNo())) my results is wrong

exemple for the "Code_Article" D000140

Normally i found 234 in Stock_reel

But i found 306 with expression with BEfore.

Thanks to the help

[rubenmarin]

The expression can be:

Top(Qte_Stock_Inventaire)+ rangesum(above(TOTAL Sum(Qte_Mvt_sum), 0, RowNo(TOTAL)))

But as said before, if you expand one article it will break the 'Above' logic, condider if you can make the chart using a straight table instead of pivot table or the option to precalculate the inventory by article and date in the script.

[#6]

Have you an exemple of script to precalculate the inventory by article and date?

thanks to the help

[rubenmarin]

Not sure if having a precalculated stock can fit your requirements but you can precalculate with something like:

// Suppposing your fact table is called 'Facts' (in this table you have all the fields):

LOAD *,

     If(RowNo()=0, Qte_Stock_Inventaire, 0) + RangeSum(Peek([stock reel acum]), Qte_Mvt_sum) as [stock reel acum]

Resident Facts order by Code_Article, Date_Mvt;

In the pivot table yo can try this expression:

FirstSortedValue([stock reel acum], -Date_Mvt)

Note that the values are precalculated so, no matter what selections you have, there will be a precalculated value for each article and date.

[Mark_Little]

HI,

If this is on one table in the load script or can be put into one then you can think about doing these calculation on load.

Using Peek() or Previous().

Something like

IF(RowNO = 1, Stock_reel,

     Previous(Stock_reel) - Qte_Mvt_sum) as RunningBalance

Mark