Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luisdarwinqlik
Contributor II
Contributor II

How sum a field for each case using another field as reference?

Hi All,

I have a doubt to finish a task, I hope you can help to solve it.

I have the next dataset:

Id BusinessDateAmountSequencial
A-123Sep 1, 2018            7,352                    1
A-123Sep 2, 2018            3,042                    2
A-123Sep 3, 2018            4,534                    3
A-123Sep 4, 2018            7,396                    4
A-123Sep 5, 2018            1,560                    5
A-456Sep 1, 2018            3,131                    1
A-456Sep 2, 2018            7,445                    2
A-456Sep 3, 2018            3,286                    3
A-789Sep 1, 2018            8,252                    1
A-789Sep 2, 2018            8,784                    2

What I'm looking for is to get:

1) Total Amount only for each Id Business but additional to this Id Business with the last sequencial:

     Is the sum of the next records


Id BusinessDateAmountSequencial
A-123Sep 1, 2018            7,352                    1
A-123Sep 2, 2018            3,042                    2
A-123Sep 3, 2018            4,534                    3
A-123Sep 4, 2018            7,396                    4
A-123Sep 5, 2018            1,560                    5
A-456Sep 1, 2018            3,131                    1
A-456Sep 2, 2018            7,445                    2
A-456Sep 3, 2018            3,286                    3
A-789Sep 1, 2018            8,252                    1
A-789Sep 2, 2018            8,784                    2

Getting as result: 1,560 + 3,286 + 8,784 = 13,630 (That's the number that I'm looking for get a show it in a label)


2) If is possible to I want to know how I could Include it the Table as two new fields as the Last Negotiated Amount and Last Negotiated Date

Id BusinessDateAmountSequencialLast Negotiated AmountLast Negotiated Date
A-123Sep 1, 2018            7,352                    11,560
Sep 5, 2018
A-123Sep 2, 2018            3,042                    21,560
Sep 5, 2018
A-123Sep 3, 2018            4,534                    31,560
Sep 5, 2018
A-123Sep 4, 2018            7,396                    41,560
Sep 5, 2018
A-123Sep 5, 2018            1,560                    51,560
Sep 5, 2018
A-456Sep 1, 2018            3,131                    13,286Sep 3, 2018
A-456Sep 2, 2018            7,445                    23,286Sep 3, 2018
A-456Sep 3, 2018            3,286                    33,286Sep 3, 2018
A-789Sep 1, 2018            8,252                    18,784Sep 2, 2018
A-789Sep 2, 2018            8,784                    28,784Sep 2, 2018

I hope to solve my doubt about this case

Thanks in advance

Regards

Luis

1 Solution

Accepted Solutions
neelamsaroha157
Specialist II
Specialist II

5 Replies
neelamsaroha157
Specialist II
Specialist II

Check this out -

vishsaggi
Champion III
Champion III

Can we get this in the front end by any chance Neelam just curious? stalwar1‌ just in case any ideas from your end.

neelamsaroha157
Specialist II
Specialist II

Yes it can be.

Aggr(NODISTINCT FirstSortedValue(Amount, -Date1), [Id Business])

vishsaggi
Champion III
Champion III

Awesome. Thanks.

luisdarwinqlik
Contributor II
Contributor II
Author

Thank you very much for your support, I really appreciate it!

Best Regards

Luis