Error in Cumulative Sum in Qliksense Table

[YashPatel0106]

Hi All,

I am facing some issue while calculating cumulative sum in Qliksense Table.

Following is the expression :

Sum(Aggr(RangeSum(Above(If(Column(5)='No'
,0,If(OrderDate=Date(BillDate,'DD-MM-YYYY'),Sum(OrderQty)-Sum(BilledQty),Sum(OrderQty))),0, RowNo())),DepotCode,MaterialNo,OrderDate,Key))

where Column(5) is the inscope column 

Expected Cumulative Remaining Qty

0

0

3

8

Please give your valuable feedback and Thanks in Advance.

Regards,

Yash P.

[sunny_talwar]

You can add column reference within the Aggr() function... you need to replace Column(5) with the actual expression behind that column.

[YashPatel0106]

We Tried that too but it gives the same output.

Following the expression :

Sum(Aggr(RangeSum(Above(If(if((((Norm)/Right(MaterialNo,6))*1000) >0 and Num(Sum(OrderQty),'#,##0.00') <(0.33 * (((Norm)/Right(MaterialNo,6))*1000) ),'Yes','No')='No',0,If(OrderDate=Date(BillDate,'DD-MM-YYYY'),Sum(OrderQty)-Sum(BilledQty),Sum(OrderQty))), 0, RowNo())),DepotCode,MaterialNo,OrderDate,Key))

//If Inscope value is 'No' then remaining qty will be zero and if orderdate and billing date is not same then remaining qty will be same as order qty.

[sunny_talwar]

What is Key here? may be you need to sort Key

DepotCode, MaterialNo, OrderDate, (Key, (NUMERIC))

[YashPatel0106]

Key is the composite of Material and Invoice no used to join Order table and billed table from where Billed Qty is coming.

Cumulative Sum need to be aggregated when DepotCode , Materialno and Orderdate are same.

[sunny_talwar]

You might have already tried this, but is this not working?

RangeSum(Above(
  If(Column(5) = 'No', 0, 
    If(OrderDate = BillDate, 
      Sum(OrderQty) - Sum(BilledQty), 
      Sum(OrderQty)
    )
  )
, 0, RowNo()))

[YashPatel0106]

No, It is not working, below is the screenshot.

[sunny_talwar]

Would you be able to share a sample to check this out?

[YashPatel0106]

PFA sample app.

[sunny_talwar]

What selections do I make to see the above scenarios?