Last Value Based on another fields value

ElPantera — Mon, 08 Feb 2021 13:47:10 GMT

Hi All,

I have been working on this problem for a while and I am open to all suggestions!

The product has a re-occurring income model which takes a % of the vending amount as well as a flat fee every month, the user gets paid out the remainder of the vending amount. The product can consume an amount which is less than the flat which creates a "debt" this debt is then carried over to the next transaction to be re-couped.

The "debt" can then be recovered as we see below in transactions occuring.

Product 1 -

Serial Number	Date	Vending	Monthly Rate	PERCENTAGE AMOUNT	SUB TOTAL	DEBT BF	DUE	DEBT CF	User payout
B132	01/01/2021	10	23	1.50	-14.50	0	-14.15	-14.15	0
B132	01/02/2021	30	23	4.50	2.50	-14.15	-10.6	-10.6	0
B132	01/03/2021	45	23	6.75	15.25	-10.6	0	0	4.65
B132	01/04/2021	35	23	5.25	6.75	0	0	0	6.75

Now on on product 2 below we can see this product hasn't recouped all of its "debt" and still an amount outstanding.

Product 2 -

Serial Number	Date	Vending	Monthly Rate	PERCENTAGE AMOUNT	SUB TOTAL	DEBT BF	DUE	DEBT CF	User Payout
C987	01/01/2021	10	23	1.50	-14.50	0	-14.15	-14.15	0
C987	01/02/2021	30	23	4.50	2.50	-14.15	-10.6	-10.6	0
C987	01/03/2021	10	23	1.50	-14.50	-10.6	0	-25.10	0.00
C987	01/04/2021	15	23	2.25	-10.25	-25.1	0	-35.35	0

What I am trying to workout is how can I look at every product in the dataset and only return the last DEBT CF amount if the user payout is below zero.

I have tried the LastValue function but this returns every products last DEBT CF, which isn't entirely true as above product 1 above wouldn't have any DEBT CF as it has broken-even with the User payout now above 0.

Any suggestions?

Re: Last Value Based on another fields value

ElPantera — Thu, 11 Feb 2021 07:03:20 GMT

No-one got any ideas?

topic Re: Last Value Based on another fields value in App Development

Last Value Based on another fields value

Re: Last Value Based on another fields value