Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
RecordID | Transaction | Due Date | GBP Net | GWP Total |
1 | t1 | 01/01/2018 | £ 2.00 | £ 100.00 |
1 | t2 | 05/11/2018 | £ 3.00 | £ 100.00 |
1 | t3 | 25/12/2018 | £ 5.00 | £ 100.00 |
2 | t1 | 11/05/2018 | £ 5.00 | £ 150.00 |
2 | t2 | 01/02/2019 | £ 5.00 | £ 150.00 |
3 | t1 | 07/07/2017 | £ 10.00 | £ 500.00 |
3 | t2 | 18/11/2018 | £ 5.00 | £ 500.00 |
3 | t3 | 25/06/2019 | £ 3.00 | £ 500.00 |
3 | t4 | 01/03/2020 | £ 1.00 | £ 500.00 |
Maye be :
=sum(total<RecordID>[GBP Net])/sum([GWP Total])
output :
Maye be :
=sum(total<RecordID>[GBP Net])/sum([GWP Total])
output :
@david_pearson GBP Net & GWP Total are your expressions or ready field available in your data?