Reverse Calculation

[idehen]

Hello Everyone,

Using: CR XI

Example of Actual Data: Grouped at ID Level

Id Amt price total
1 220.00 1.205 265.5
2 50.568 1.205 609.93444
3 85.29 1.205 2473.41


Sum total=3348.84444

Though above is the correct sum total.

However, is there a way of doing a reverse sum if i had an expected total of 3348.8445.

I know i could use rounding or similar function at sum total level, though what i would want changed is the values that make up the Sum total to get the expected total.

So Totals would appear by calculation as:

265.5
609.9345
2473.41

Sum total: 3348.8445

Any help appreciated guys.

Thanks

Sol

 

[lbass]

I guess the question is why adjust that particular detail value? Do you just want to round up? If so, you can use the roundup() function.

You could replace the detail value with a formula like this:

if {table.value} = maximum({table.value},{table.group}) then
{@yourexpectedvalue} - (sum({table.value},{table.group})-{table.value}) else
{table.value}

This would adjust the largest value in the group. Or you could say:

if {table.ID = 2 then //etc.

...if you want to adjust a specific value.

-LB

 

[idehen]

Thanks as always LB.

if {table.value} = maximum({table.value},{table.group}) then
{@yourexpectedvalue} - (sum({table.value},{table.group})-{table.value}) else
{table.value}

worked perfect.
-----------------------------------------------------------
I used somehow same but different logic earlier and am just glad your suggestion though shorter and neater is close enough to what i used.

What i did was create 3 formulae where:

Formula name: Val1
{Expected Totalvalue} - (sum({table.value},{table.group})

Formula name2: Val2
Maximum({Table.Value})

Formula name3: Val3
{Val1} - {Val2}

Group total sum of Val3 will give me value expected.

-------------------------------------------------------------
But your logic helps to avoid creating too many formula everywhere and just using one instead.

Many thanks again LB, much appreciated.