topic Re: Pivot Table wrong total | Problem with expression in QlikView

Pivot Table wrong total | Problem with expression

dominikkeller — Sat, 16 Nov 2024 00:21:39 GMT

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?

Re: Pivot Table wrong total | Problem with expression

marcus_sommer — Wed, 08 Jul 2020 13:51:58 GMT

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

Re: Pivot Table wrong total | Problem with expression

dominikkeller — Wed, 08 Jul 2020 16:17:43 GMT

Thank you!

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