topic Splitting a value to two decimal places using AGGR function with no remainder in App Development

Splitting a value to two decimal places using AGGR function with no remainder

jonbshaw — Mon, 20 Dec 2021 21:08:46 GMT

Hi,

Please could someone help me find the solution to the below problem.

=Sum([Gross Amt])/aggr(NODISTINCT count([OrderRef]),[Invoice Number])

Gives me 3,185.54 split exactly 6 ways to equal 530.9233333 on each row. I need the values to be to two decimal places, but the value has to be exactly 3,185.54, there can't be any remainder, so all of the rows can't equal 530.92 or 530.93

What I have:
Invoice Number	Gross Amt	Order Ref	=Sum([Gross Amt])/aggr(NODISTINCT count([OrderRef]),[Invoice Number])
901031	3185.54	28935801	530.9233333
901031	3185.54	28937601	530.9233333
901031	3185.54	28937701	530.9233333
901031	3185.54	28937901	530.9233333
901031	3185.54	28939301	530.9233333
901031	3185.54	28946801	530.9233333

What I need:
Invoice Number	Gross Amt	Order Ref	=Sum([Gross Amt])/aggr(NODISTINCT count([OrderRef]),[Invoice Number])
901031	3185.54	28935801	530.93
901031	3185.54	28937601	530.93
901031	3185.54	28937701	530.92
901031	3185.54	28937901	530.92
901031	3185.54	28939301	530.92
901031	3185.54	28946801	530.92

Any help would be very much appreciated.

Regards,

Jon

Re: Splitting a value to two decimal places using AGGR function with no remainder

Vegar — Fri, 06 Nov 2020 05:56:49 GMT

I don't see how you can do what you want to do, but even if all your rows displays 530.92 your totalling should be correct as long you do not change the totalling from the default (expression total) to Sum of Rows.

Re: Splitting a value to two decimal places using AGGR function with no remainder

jonbshaw — Mon, 09 Nov 2020 01:05:50 GMT

Thanks very much for your response.

I need to be able to upload this output into a separate system, so it has to be exact, like in my 'What I need' table.

The total in the Gross Amt column is an invoice total so it can't be changed, the numbers I need in the new column have to equal the Gross Amt when added together, and they have to be to two decimal places.