Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

cumulative sum by customer

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

cumulative.png

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

I can walk on water when it freezes
16 Replies
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
Not applicable

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

ali_hijazi
Partner - Master II
Partner - Master II
Author

check attached doc

I can walk on water when it freezes
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)))

Capture.PNG

ali_hijazi
Partner - Master II
Partner - Master II
Author

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?

I can walk on water when it freezes
ali_hijazi
Partner - Master II
Partner - Master II
Author

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

I can walk on water when it freezes
sunny_talwar

When you open my file it isn't working?

ali_hijazi
Partner - Master II
Partner - Master II
Author

your file is working because document is reduced

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

cumulative_2.png

I can walk on water when it freezes