Sum of Expression is not coming correctly

[kartikaysingh]

Hi,

My expression is as below.

=if(REVENUE_HEAD='FLEET' ,
(1+ sum(RISK_EXP_INC-TOTAL_PRIOR_PREM)/sum(TOTAL_PRIOR_PREM))
/
(1+ sum(RISK_EXP_INC-RISK_RATE)/sum(RISK_RATE) )
-1
,if(RH = 'Total Commercial Property' or RH = 'Total Commercial Casualty' or RH = 'Risks without NCD Normalisation'
or R1 = 'Total Commercial - Non Motor' or REVH = 'Motor Trade Road Risk'
,
sum(TOTAL_PRIOR_PREM * RISK_EXP_INC)/sum(TOTAL_PRIOR_PREM) 
,'-') )

Customer is trying to match my data with his. The problem is that row by row figures are matching but when he is summing up in excel, it is not matching with my sum in qlikview which I have done as sum of expression. I tried sum of rows but definitely is not matching with expected.

Main focus point is in the below calculation:

sum(TOTAL_PRIOR_PREM * RISK_EXP_INC)/sum(TOTAL_PRIOR_PREM) 

Can anyone help in aggregating this expression, or is there any other way to solve 'sum of expression' problem.

[dplr-rn]

Hard to diagnose without access to data.
My question to you is.... if below is what the customer is doing in excel? (i.e. multiplying the sums rather than multiplying the individual rows and summing them)

(sum(TOTAL_PRIOR_PREM) *sum( RISK_EXP_INC))
/sum(TOTAL_PRIOR_PREM)

[kartikaysingh]

Hi, I did tried doing like you have shown but that is not giving the desired total. I have attached the data. -2.885% total is coming from the Qlikview report whereas -2.294 is coming from the excel which customer did. Line by line figures are matching as you can in the spreadsheet. Let me know if you have a solution.

[mjtaft2017]

---

@kartikaysingh wrote:

Hi, I did tried doing like you have shown but that is not giving the desired total. I have attached the data. -2.885% total is coming from the Qlikview report whereas -2.294 is coming from the excel which customer did. Line by line figures are matching as you can in the spreadsheet. Let me know if you have a solution.

---

The calculation in column L is taking the columns Post Renewal Exposure/Pre Renewal Exposure - 1

=H3/G3-1

which doesn't match your calculation sum(TOTAL_PRIOR_PREM * RISK_EXP_INC)/sum(TOTAL_PRIOR_PREM)

=sum(F3*D3)/sum(F3)

If you take F3 * D3 you get column I which is Risk Rate and the first part of your calculation.

If you take sum Column I / sum Column F (Total Prior Premium) then the result matches your calculation
and also your Qlik result of  -2.885 (after you format the cell as percent  - initially it is -0.028853054)