Skip to main content
Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## Pivot Table wrong total | Problem with expression

Hey
i have a problem with my expression.

I build an overview of who has lent whom for how many hours. This is used for internal billing.

However, I have an exception. If the lender and recipient have a certain constellation, the wage factor is at 13.5, otherwise at 18. In the picture you can see that my total is incorrect. I just don't come up with the solution in my formula.

``````if( /*Lender*/AbrechnungsDISPLAYNAME='MA Oldenburg' and /*Reciever*/ArbeitsDISPLAYNAME='CBC Oldenburg',
(ceil((sum({\$</*Dismiss Working Area */KLV_Master.datev_nr-={\$(=1015)}>}/* Working Seconds */KLV_Master.gesamt_std_in_sek)/60/60),0.25)*/* Wage factor */13.5),
(ceil((sum({\$</*Dismiss Working Area */KLV_Master.datev_nr-={\$(=1015)}>}/* Working Seconds */KLV_Master.gesamt_std_in_sek)/60/60),0.25)*/* Wage factor */18)) ``````

The images:

1: The version i need for my further calc.

2: Just to show my Problem

Any Ideas?

Labels (2)

• ### Pivot Table Expression

1 Solution

Accepted Solutions
MVP & Luminary

Your expression is not just a straight aggregation else some kind of rate with conditions. The dimensionality on which your conditions are based didn't exists / aren't properly applied in the total-calculation and therefore the total didn't show the sum of the displayed rows. To get this result you need to wrap your expression within an aggr() like:

sum(aggr(YourExpression, Dim1, Dim2))

whereby Dim1 und Dim2 are placeholder for the dimensional context on which the calculation should happens - usually it are the dimensions within your chart like Lender, Arbeits... but you may need further dimensions like period-fields or similar if they are essential in the calculation-context.

- Marcus

2 Replies
MVP & Luminary

Your expression is not just a straight aggregation else some kind of rate with conditions. The dimensionality on which your conditions are based didn't exists / aren't properly applied in the total-calculation and therefore the total didn't show the sum of the displayed rows. To get this result you need to wrap your expression within an aggr() like:

sum(aggr(YourExpression, Dim1, Dim2))

whereby Dim1 und Dim2 are placeholder for the dimensional context on which the calculation should happens - usually it are the dimensions within your chart like Lender, Arbeits... but you may need further dimensions like period-fields or similar if they are essential in the calculation-context.

- Marcus

Contributor III
Author

Thank you!

It worked perfect with the Dim's Lender and Reciever.

Community Browser