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

luisdarwinqlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

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

I have the next dataset:

Id Business	Date	Amount	Sequencial
A-123	Sep 1, 2018	7,352	1
A-123	Sep 2, 2018	3,042	2
A-123	Sep 3, 2018	4,534	3
A-123	Sep 4, 2018	7,396	4
A-123	Sep 5, 2018	1,560	5
A-456	Sep 1, 2018	3,131	1
A-456	Sep 2, 2018	7,445	2
A-456	Sep 3, 2018	3,286	3
A-789	Sep 1, 2018	8,252	1
A-789	Sep 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 Business	Date	Amount	Sequencial
A-123	Sep 1, 2018	7,352	1
A-123	Sep 2, 2018	3,042	2
A-123	Sep 3, 2018	4,534	3
A-123	Sep 4, 2018	7,396	4
A-123	Sep 5, 2018	1,560	5
A-456	Sep 1, 2018	3,131	1
A-456	Sep 2, 2018	7,445	2
A-456	Sep 3, 2018	3,286	3
A-789	Sep 1, 2018	8,252	1
A-789	Sep 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 Business

Date

Amount

Sequencial

Last Negotiated Amount

Last Negotiated Date

A-123

Sep 1, 2018

7,352

1,560

Sep 5, 2018

A-123

Sep 2, 2018

3,042

1,560

Sep 5, 2018

A-123

Sep 3, 2018

4,534

1,560

Sep 5, 2018

A-123

Sep 4, 2018

7,396

1,560

Sep 5, 2018

A-123

Sep 5, 2018

1,560

Sep 5, 2018

A-456

Sep 1, 2018

3,131

3,286

Sep 3, 2018

A-456

Sep 2, 2018

7,445

3,286

Sep 3, 2018

A-456

Sep 3, 2018

3,286

Sep 3, 2018

A-789

Sep 1, 2018

8,252

8,784

Sep 2, 2018

A-789

Sep 2, 2018

8,784

Sep 2, 2018

I hope to solve my doubt about this case

Thanks in advance

Regards

Luis

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

neelamsaroha157 — Fri, 07 Sep 2018 18:22:36 GMT

Check this out -

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

vishsaggi — Fri, 07 Sep 2018 20:44:38 GMT

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

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

neelamsaroha157 — Fri, 07 Sep 2018 20:50:46 GMT

Yes it can be.

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

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

vishsaggi — Fri, 07 Sep 2018 21:00:54 GMT

Awesome. Thanks.

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

luisdarwinqlik — Mon, 10 Sep 2018 13:32:42 GMT

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

Best Regards

Luis

topic Re: How sum a field for each case using another field as reference? in QlikView

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

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

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

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

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

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