topic Re: Range Sum Expression Help. in QlikView

Range Sum Expression Help.

bnelson111 — Wed, 12 Mar 2014 14:40:25 GMT

I have a Straight Table as below.

As you can see the WIP is a sum from ODBC table against the Pcodea and i'm attempting to have a range sum of it down dating against the new order qty (which is the customers orders). Problem is it continues to down date against a different Pcodea. As you can see it continues when Pcodea changes from 230325380 to 230325432. Code Below.

Any ideas?

Re: Range Sum Expression Help.

MK_QSL — Wed, 12 Mar 2014 14:53:46 GMT

Try this

Aggr(Your expression, Pcodea)

UPDATE :

Try this..

SUM([Your Jobline Field] -RangeSum(Above(SUM({<CODE>}OrderQTY),0,RowNo(TOTAL)))

Change your fieldname accordingly...

Re: Range Sum Expression Help.

bnelson111 — Wed, 12 Mar 2014 16:23:09 GMT

Using the Aggr(Your expression, Pcodea) i get the following.

Using the SUM([Your Jobline Field] -RangeSum(Above(SUM({<CODE>}OrderQTY),0,RowNo(TOTAL)))

the table opens all Pcodea's

Re: Range Sum Expression Help.

MK_QSL — Wed, 12 Mar 2014 16:26:20 GMT

Use my second option

SUM([Your Jobline Field] -RangeSum(Above(SUM({<CODE>}OrderQTY),0,RowNo(TOTAL)))

Re: Range Sum Expression Help.

bnelson111 — Wed, 12 Mar 2014 16:39:45 GMT

Brilliant. Thanks

Re: Range Sum Expression Help.

bnelson111 — Thu, 13 Mar 2014 11:14:59 GMT

Just messed it up??

([Jobline for Customer Aisling;.OutstandingQty])-rangesum(above(Sum({<[Pcodea]>}[new_order_qty]),0,RowNo(TOTAL)))

As you can see the row two balance should read 27600..

Re: Range Sum Expression Help.

MK_QSL — Thu, 13 Mar 2014 11:19:43 GMT

Can you load your sample file?

Re: Range Sum Expression Help.

bnelson111 — Thu, 13 Mar 2014 11:31:04 GMT

Manish I'm using a personnal version. It's got a lot of other tables on different tabs. Would it help if I take screen shot of dim and expressions ? Sorry.

Re: Range Sum Expression Help.

MK_QSL — Thu, 13 Mar 2014 11:37:58 GMT

You can always save the another version on your file and delete unwanted tabs.

Sometimes it is difficult to give you result without checking apps as above is working in my file...

Re: Range Sum Expression Help.

bnelson111 — Thu, 13 Mar 2014 11:40:58 GMT

Will do. Send in 15mins. Someone is using my pc. Thanks.

Re: Range Sum Expression Help.

bnelson111 — Thu, 13 Mar 2014 12:05:11 GMT

Hopefully to have a share. file is send.qvd

let me know if you have received, never loaded file before..

Re: Range Sum Expression Help.

bnelson111 — Thu, 13 Mar 2014 12:08:29 GMT

Re: Range Sum Expression Help.

bnelson111 — Thu, 13 Mar 2014 12:25:23 GMT

Might have it.

([Jobline for Customer Aisling;.OutstandingQty])-rangesum(above(TOTAL(SUM({<[Pcodea]>}[new_order_qty])),0,NoOfRows(TOTAL)))

Re: Range Sum Expression Help.

bnelson111 — Thu, 13 Mar 2014 12:28:37 GMT

No its continuing on different pcodea ......

Re: Range Sum Expression Help.

bnelson111 — Thu, 13 Mar 2014 13:45:10 GMT

Thankyou for all your help very much appreciated, just got it. Rebuilt the chart and the expression was fine.

Again thankyou.

sum([Jobline for Customer Aisling;.OutstandingQty])+sum([Inventory Stock For Customer Report Aisling;.InventoryQty]))
-rangesum(above(sum({<[Pcodea]>}[new_order_qty]),0,NoOfRows(TOTAL)))