topic Straight table running total in QlikView

Straight table running total

mjamesking — Wed, 30 Sep 2015 22:30:18 GMT

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

Re: Straight table running total

settu_periasamy — Thu, 01 Oct 2015 05:25:00 GMT

Hi,

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

You can use the below expression

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

Re: Straight table running total

Gysbert_Wassenaar — Thu, 01 Oct 2015 05:25:19 GMT

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

Re: Straight table running total

mjamesking — Fri, 02 Oct 2015 22:41:19 GMT

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

Re: Straight table running total

Gysbert_Wassenaar — Sat, 03 Oct 2015 05:30:38 GMT

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())

Re: Straight table running total

mjamesking — Wed, 07 Oct 2015 05:08:31 GMT

Thanks Gysbert, this works nicely.