Dimensionality ? Wrong sum in Pivot

[#1]

I have a problem I can't seem to find the solution to.

I have a pivot table showing turnover and invoiced contra budget. The turnover tallies but the invoiced shows wrong amount on detail level (ok on Total).

The reason for this is that on budget and turnover level I have a Claim_Type at Client_Id level and one of the Client_Ids (the ones ending on 1) have several Claim_Type (1 and 9). This Claim_Type dosen't exists in the Invoice table which is based solely on Client_Id. Therefore QV dosen't know which Claim_Type to put invoiced amount on and puts it on both.

The amount on Claim_Type 9 with the red ring around it in the enclosed is incorrect.

I have tried to fake a Claim_Type in the invoice table and use it in expression, but it just multiplies the invoiced amount. I have also tried to make a Dimensionality in order to only get total amount but it requires that the user then select invoice date also besides budget date which then de-selects Client_ids where invoiced =0 which is also wrong.

Anybody has any ideas who to do this ?

[#2 · Accepted solution]

I found the solution to my problem. Instead of Sum(if(..... it should be If(.... , sum.... This makes the correct summation:

IF(CLAIM_TYPE=9, 0, SUM(if(TurnoverBudgetMonth=Ledgerinvoice_InvoiceMonthYear,(LedgerInvoice_Amount))))