cumulative sum by customer

[ali_hijazi]

Hello got the following pivot table with dimensions(Customer, Customer Rout, Invoice, Invoice date, and invoice due date

I want to calculate the cumulative sum of invoices by customer

I tried the following expression:

$(vOpenInvoice_NetAmt) + RangeSum(Above(TOTAL $(vOpenInvoice_NetAmt),1,RowNo(TOTAL)))

yet this works fine only if I select one customer with multiple customers the amount is accumulation is full

is there another approach or I should replace rowno(total) with something else? as RowNo(Total) ignores all dimensions

[sunny_talwar]

I think you are not looking to do a full accumulation by all dimensions, right? You will have to play around with the Aggr() function to make this work. Would you be able to share a sample to look at this?

[Anonymous]

Max(Total Aggr(Sum([vOpenInvoice_NetAmt]), Customer))

[ali_hijazi]

check attached doc

[sunny_talwar]

If you have QV12, this can work:

Aggr(RangeSum(Above($(vOpenInvoice_NetAmt),0,RowNo())), CustomerCode,

(InvoiceNum, (=only({1<TrxType={'sales','sales returns'}, DocStatus={'O'},TrxDate=,DAY=,MONTH=,MONTH_NUM=,MONTH_YEAR=,YEAR=,QUARTER=,CusName=$::CusName,CustomerCode=$::CustomerCode,TrxDefaultSaleman1=$::TrxDefaultSaleman1,TrxDefaultSaleman2=$::TrxDefaultSaleman2,TrxDefaultSaleman3=$::TrxDefaultSaleman3>}DocDueDate), ASC)))

[ali_hijazi]

this is a new syntax!

this works only on version 12?

so I need to download the latest version as I'm on version 11.2 SR8

is there a place where I can learn about the latest syntax modifications?

[sunny_talwar]

Right here:

Recipe for a Pareto Analysis – Revisited

[ali_hijazi]

it is working on your copy but on the original one that I have it didn't work

I upgraded my qlikview to 12.1

please advise

[sunny_talwar]

When you open my file it isn't working?

[ali_hijazi]

your file is working because document is reduced

but when I copied the expression to mine it is giving null at some rows: