Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
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)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
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

View solution in original post

2 Replies
Highlighted
MVP & Luminary
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

View solution in original post

Highlighted
Contributor III
Contributor III

Thank you! 

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