Straight table running total

[mjamesking]

Hi,

I would like to create a straight table in the following format

Stock	Stock Value	Weight	Running Weight Total
Bananas	1	0.3333	0.3333
Apples	2.2	0.2222	0.5555
Pears	3.13	0.1	0.6555
Peaches	4.12	0.1	0.7555
Apricots	5.88	0.15	0.9055
....	....	....	.....

Where I hold the Stock, Value and Weight in my data model and I'm calculating the Running Weight total where the Running Weight Total is the SUM of all the Weights of each Stock where the Stock Value is less than or equal to the Stock Value of the Stock I'm calculating it for.

I'd like to be able to do this in a chart expression rather than in the script so that I can chop and change the stocks that are in scope for the calculation and have the Running Weight Total recalculate.

Any ideas?

Thanks

Matt

[settu_periasamy · Accepted solution]

Hi,

You can try this in two way. In Expression Tab -> Full Accumulation

OR

You can use the below expression

=Rangesum(Above(TOTAL Sum(Weight),0,rowno(TOTAL)))

[Gysbert_Wassenaar]

Try rangesum(above(sum(Weight),0,rowno())) as expression for the Running Weight Total

[mjamesking]

Thanks both - can you think of how to highlight the first row where a threshold is breached. For instance if I set a threshold as 50% of the total 'weight' and sort the table by value I want to highlight the first row where the running weight total goes above that value (this gives me a weighted median)

I've tries using combinations of FirstSortedValue and AGGR functions but can't get this to work.

Thanks
Matt

[Gysbert_Wassenaar]

You have an expression that calculates the values in the Above threshold column. You can use that in an expression to highlight the cell using the above() function. Something like:

=if(  above(...above_threshold_expression...)=0 and ...above_threshold_expression...=1  , yellow())

[mjamesking]

Thanks Gysbert, this works nicely.