Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
david_pearson
Contributor III
Contributor III

sum() field and divide by one instance of another

Hi all, hopefully i can explain this well enough that someone can help me. i have a flat file loaded into QS.  Basic example below. Each RecordID can have multiple transaction. What im trying to do is, for each Record ID, sum the GBP Net field and then divide by the first instance of this GWP Total. So Record ID 1 would be =10/100, Record ID 2 =10/150 etc but i'm struggling to get this to work. I want to achieve this in a chart expression. the GWP total amount will be the same on every line for one Record ID and doesnt change line by line, it will only vary by Record ID. Can anyone help? p.s. the Record ID isnt likely to be in the correct running order in the base data but i can load this the correct order if required. 

RecordIDTransactionDue DateGBP NetGWP Total
1t101/01/2018 £       2.00 £    100.00
1t205/11/2018 £       3.00 £    100.00
1t325/12/2018 £       5.00 £    100.00
2t111/05/2018 £       5.00 £    150.00
2t201/02/2019 £       5.00 £    150.00
3t107/07/2017 £    10.00 £    500.00
3t218/11/2018 £       5.00 £    500.00
3t325/06/2019 £       3.00 £    500.00
3t401/03/2020 £       1.00 £    500.00

 

1 Solution

Accepted Solutions
Taoufiq_Zarra

@david_pearson 

Maye be :

=sum(total<RecordID>[GBP Net])/sum([GWP Total])

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

@david_pearson 

Maye be :

=sum(total<RecordID>[GBP Net])/sum([GWP Total])

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kushal_Chawda

@david_pearson  GBP Net  & GWP Total are your expressions or ready field available in your data?