Total amount wrong calculated

[Thomas_G]

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.

[vighnesh_gawad]

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

[Thomas_G]

Thank you for your reply.

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

[marcus_sommer]

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).

[Thomas_G]

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

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

[marcus_sommer]

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.

[Thomas_G]

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?

[BIAKS]

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
)

[marcus_sommer]

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. 

[Thomas_G]

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.