Hi,
As you may have been able to tell from the title, I'm not quite sure how to go about this, so I'm hoping that someone with a bit of expertise could help!
I basically want to end up with a straight table, with six separate calculations, which build on each other.
The first six columns of my example table are the data I'm loading in, the last six (in purple) are the results I'm hoping to achieve in QV.
| ID | Type | Size | Name | Score | Old | New | R1 | R2 | R3 | R4 | R5 | R6 |
|---|
| 1877 | A | 10 | 1 | 40.67 | 29 | 30 | 0.08 | 0.08 | -0.51 | -2.53 | -1.25 | -1.25 |
| 3206 | A | 10 | 1 | 40.00 | 28 | 20 | -0.82 | -0.82 | -0.51 | -2.53 | -1.25 | -1.25 |
| 7633 | A | 10 | 1 | 40.67 | 29 | 20 | -0.92 | -0.92 | -0.51 | -2.53 | -1.25 | -1.25 |
| 9454 | A | 10 | 1 | 43.33 | 33 | 40 | 0.71 | 0.28 | -0.51 | -2.53 | -1.25 | -1.25 |
| 9816 | A | 10 | 1 | 37.60 | 26 | 10 | -1.58 | -1.58 | -0.51 | -2.53 | -1.25 | -1.25 |
| 3917 | A | 10 | 2 | 42.67 | 30 | 20 | -0.99 | -0.64 | 0.01 | 0.04 | -1.25 | -1.25 |
| 6319 | A | 10 | 2 | 39.33 | 23 | 30 | 0.70 | 0.35 | 0.01 | 0.04 | -1.25 | -1.25 |
| 8833 | A | 10 | 2 | 44.67 | 34 | 30 | -0.40 | -0.40 | 0.01 | 0.04 | -1.25 | -1.25 |
| 9454 | A | 10 | 2 | 43.33 | 31 | 30 | -0.14 | 0.28 | 0.01 | 0.04 | -1.25 | -1.25 |
| 9828 | A | 10 | 2 | 48.67 | 41 | 50 | 0.86 | 0.86 | 0.01 | 0.04 | -1.25 | -1.25 |
| 2670 | B | 5 | 1 | 43.33 | 14 | 10 | -0.42 | -0.42 | -0.48 | -1.45 | -1.26 | -1.25 |
| 3917 | B | 5 | 1 | 42.67 | 13 | 10 | -0.30 | -0.64 | -0.48 | -1.45 | -1.26 | -1.25 |
| 6319 | B | 5 | 1 | 39.33 | 10 | 10 | -0.01 | 0.35 | -0.48 | -1.45 | -1.26 | -1.25 |
| 4436 | B | 5 | 2 | 41.33 | 8 | 0 | -0.75 | -0.75 | -0.49 | -0.98 | -1.26 | -1.25 |
| 6219 | B | 5 | 2 | 40.67 | 7 | 10 | 0.26 | 0.26 | -0.49 | -0.98 | -1.26 | -1.25 |
R1: =(New - Old)/Size
R2 = Average of R1 for each distinct ID
R3 = Sum of R1 for each distinct Name / Count of all (non distinct) IDs in that distinct Name
R4 = R3 for each distinct Name x Count of all (non distinct) IDs in that distinct Name
R5 = Sum of R4 for each distinct Type / Count of all (non distinct) IDs in that distinct Type
R6 = Sum of R4 / Count of all (non distinct) IDs
Unsurprisingly, I've managed to workout how to calculate R1 myself (New-Old)/Size
But that's it...
The list of IDs, Types, and Names is endless, so I don't want to have to refer to (e.g.) 'Type=A' anywhere, if I can avoid it.
I toyed with the idea of creating a variable from my R1 expression, but then realised that I'm a bit out of my depth, so thought I'd be best off seeing if anyone had any better suggestions first!
Any suggestions would be greatly appreciated - please let me know if you need any additional information.
