topic Total amount wrong calculated in App Development

Total amount wrong calculated

Thomas_G — Wed, 25 Feb 2026 16:24:34 GMT

If i use the "IF...Then" statement in a pivot table to control how the value must be calculated the total amount of this column is wrong.

The potential sales quantity should be determined based on the order backlog and the currently available quantity.

If the quantity on stock is less then the backlog order quantity of the item the quantity on stock should be used, otherwise the backlog quantity.

=If(
sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}Stock_Quant) < sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}BCK_Quant),

//Then
sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}Stock_Quant)
,

//Else
sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}BCK_Quant))
)

The total value for the "potential sales quantity" shows the total of the backlog quantity.

But there are many items with less or zero quantity on stock. So the calculated "potential sales quantity" must be far less then the backlog quantity.

If i export the data to excel and calculate the total of the "potential sales quantity" column i get the correct quantities.

That means the calucalted value in the rows are correct but not the total value on top of the pivot table.

Strange is also, this problem only exist if there is no filter group activ.
If i filter the items by item number or item group or any other possible properties the calculation of the total is correct.

Re: Total amount wrong calculated

vighnesh_gawad — Thu, 26 Feb 2026 08:41:41 GMT

In Dimension properties are you apply correct aggression method for total as sum or is it auto?

Re: Total amount wrong calculated

Thomas_G — Thu, 26 Feb 2026 09:36:19 GMT

Thank you for your reply.

I can not find the option to define the aggregation method?
please look at the picture.

Re: Total amount wrong calculated

marcus_sommer — Thu, 26 Feb 2026 09:44:42 GMT

To ensure that your total returns the sum/avg/... of the object-rows you could wrap your calculation with an aggr(), like:

sum(aggr(myCalculation, Dim1, Dim2))

whereby Dim1 and Dim2 are just place-holder for the needed dimensional-context (usually the ones from the chart).

Re: Total amount wrong calculated

Thomas_G — Thu, 26 Feb 2026 10:12:10 GMT

Ok. But with aggr() a error is shown. -> "Aggr takes at least 2 parameters"

sum(aggr({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}Menge_vorhanden))

Re: Total amount wrong calculated

marcus_sommer — Thu, 26 Feb 2026 10:26:20 GMT

Like hinted you need to specify the dimensions in which context the calculation should be performed:

sum(aggr(myCalculation, Dim1, Dim2))

and the aggr() here is meant as a single aggr() around your if-query.

Re: Total amount wrong calculated

Thomas_G — Thu, 26 Feb 2026 11:13:41 GMT

Sorry, but i do not real understand what you mean with dimension-context.

I have tried with a much simpler formula to wrap some sum() in aggr() and otherwise. But i get wrong or null values.

This works fine:

=if(sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}AB_Betrag) > 0,
sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}Menge_vorhanden),
0)

How to include aggr() in such a working formula?

Re: Total amount wrong calculated

BIAKS — Thu, 26 Feb 2026 12:34:07 GMT

something like this maybe:

if(Sum(Aggr(sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}AB_Betrag),Artikel, Kunde, Jahr, Monat)) > 0, Sum(Aggr(sum({$<Jahr={"$(#=year(today()))"},Monat={"$(#=month(today()))"}>}Menge_vorhanden),Artikel, Kunde, Jahr, Monat)), 0 )

Re: Total amount wrong calculated

marcus_sommer — Thu, 26 Feb 2026 12:50:29 GMT

Each calculation has always a dimensional-context.

Example: a simple pivot with Customer and Period as vertical/horizontal dimension and an expression like: sum(Value) calculates n results - one for each cell respectively the dimensional-context. In such case no aggr() would be needed because the total would be always the sum of the rows.

But as far as any conditions are applied or rates calculated the total mustn't be mandatory the sum of the rows because the conditions/rates aren't equally the same for the total and each object-row.

Imagine that the Customers have an individual discount depending on previous sales or special contracts. If now an expression like: sum(RawSales * Discount) is used it would return the correct results on the row-level but the total must go wrong because it wouldn't respect the right discount. The solution would be to enforce the calculation on the needed dimensional-context and then summing the results within the total. For this example it would be:

sum(aggr(sum(RawSales * Discount), Customer))

whereby Customer is here the dimensional-context for the calculation of: sum(RawSales * Discount). Extending the considerations to an discount exclusion of zero-margin categories the expression may look like:

sum(aggr(sum(RawSales * Discount), Customer, Category))

which may not mandatory need an included dimension of the object. This means the fields which define the dimensional-context might be included within the object but mustn't as well as it's not always necessary to specify all object-fields as aggr-dimension - it depends to detailed scenario.

I suggest not to start to try to apply the aggr-approach within your origin table else creating a new one with just two dimensions and a single expression to play with it and to comprehend the logic. If this is done is adapting it to the origin table much more easy.

Re: Total amount wrong calculated

Thomas_G — Thu, 26 Feb 2026 15:28:31 GMT

Hello Marcus_Sommer,

thank you very much for the detailed explanation. But i am still not able to find a solution for the wrong total values.

So, i have created a tiny app with Inline Data, which show the problem. Attached.
The problem is also marked in the attached image.

I hope someone could find a solution.
Thanks a lot in forehand.

If not, than i will look to fix this with a new view in the database to calculate the values while loading.

Re: Total amount wrong calculated

BIAKS — Fri, 27 Feb 2026 06:08:30 GMT

You can easily solve it like this:

=Sum(Aggr( If( Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}BacklockQuant),Item,Year,Month)) > Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}OnStock),Item,Year,Month)), //Then Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}OnStock),Item,Year,Month)), //Else Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}BacklockQuant),Item,Year,Month)) ) ,Item,Year,Month))

SS:

Re: Total amount wrong calculated

marcus_sommer — Fri, 27 Feb 2026 14:28:07 GMT

Your comparing logic is - take the smaller value which is also applied on the total-level because 65 is smaller as 74. Like above hinted the totals doesn't respect the results of the row-level else they calculate their own ones. To get the sum of the rows the aggr() needs to wrapped on the outside of row-level calculation - with the right dimension-context. In a case like yours I would apply the following:

sum(aggr(rangemin(OnStock_Expr, BackLog_Expr), Item))

Re: Total amount wrong calculated

Thomas_G — Mon, 02 Mar 2026 08:59:52 GMT

Thanks a lot Marcus,

but is still note able to get this fixed with rangemin.

=sum(Aggr(
rangemin(
Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}OnStock),Item,Year,Month)),
Sum(Aggr(sum({$<Year={"$(#=year(today()))"},Month={"$(#=month(today()))"}>}OnStock),Item,Year,Month))
)
,Item,Year,Month))

But the post above from BIAKS fixed my problem.

Thanks you very much for your help and the very interesting detailed response!