topic How to calculate max amount so far in a table. in App Development

How to calculate max amount so far in a table.

AmyJan25 — Wed, 16 Apr 2025 11:11:59 GMT

Hello,

I have a table with "Date" and "Daily Revenue" and I looking to create another column for "Max Daily Revenue to Date" but having trouble making this work.

Any insights would be appreciated!!!

Date	Daily Revenue	Max Daily Revenue to Date
16-Apr-25	882	1,000
15-Apr-25	973	1,000
14-Apr-25	994	1,000
13-Apr-25	1,000	1,000
12-Apr-25	964	998
11-Apr-25	942	998
10-Apr-25	865	998
09-Apr-25	875	998
08-Apr-25	926	998
07-Apr-25	975	998
06-Apr-25	986	998
05-Apr-25	975	998
04-Apr-25	998	998
03-Apr-25	925	980
02-Apr-25	963	980
01-Apr-25	980	980

Re: How to calculate max amount so far in a table.

SRA — Wed, 16 Apr 2025 12:08:56 GMT

Hi,

Could this fits to your requirement ?

RangeMax(Above(TOTAL Sum(DailyRevenue), 0, RowNo(TOTAL)))

Sorting date ascending you will get :

Regards

Re: How to calculate max amount so far in a table.

AmyJan25 — Wed, 16 Apr 2025 14:00:54 GMT

It does help in a straight table, but ideally it would be helpful to build this logic into the load script so that I can build other rules off this amount etc. if you know how this could work?

Re: How to calculate max amount so far in a table.

SRA — Wed, 16 Apr 2025 14:26:46 GMT

You have a table agreagated by Date ? If yes, here is one example :

Source:

Load * InLine

[Date,DailyRevenue

16-04-2025, 882

15-04-2025, 973

14-04-2025, 994

13-04-2025, 1000

12-04-2025, 964

11-04-2025, 942

10-04-2025, 865

09-04-2025, 875

08-04-2025, 926

07-04-2025, 975

06-04-2025, 986

05-04-2025, 975

04-04-2025, 998

03-04-2025, 925

02-04-2025, 963

01-04-2025, 980

];

Data:

Load

Date(Date#(Date,'DD-MM-YYYY')) as Date,

DailyRevenue,

RangeMax(DailyRevenue, Peek('MaxToDate')) as MaxToDate

Resident Source

Order By Date;

Drop Table Source;

Re: How to calculate max amount so far in a table.

AmyJan25 — Wed, 16 Apr 2025 16:33:04 GMT

Found a solution by doing the below in the load script:

If(RowNo()=1, DailyRevenue, RangeMax(DailyRevenue, Peek('MaxPrice'))) as MaxPrice