Hello,
For a pivot table I was asked to return the last three months average Quality Score when the month didn't have a quality score given. I did this by using the an if statement with the rangesum(above()):
=if(IsCurrentMTD = '1', sum(WITNESS_POINTS_SCORED)/sum(WITNESS_POINTS_POSSIBLE), If(len(trim(sum(WITNESS_POINTS_SCORED)/sum(WITNESS_POINTS_POSSIBLE)))= 0 ,
RangeSum(Above(sum(WITNESS_POINTS_SCORED), 1,3))/RangeSum(Above(sum(WITNESS_POINTS_POSSIBLE), 1,3)), sum(WITNESS_POINTS_SCORED)/sum(WITNESS_POINTS_POSSIBLE)))
However there will be instances where there may also be nulls or no data in the preceding three months as well. I need my expression to take the last three months that have data or are not null. How do I adjust my above expression to account for those scenarios? Below is an example. QA^^ is the field where I used RangeSum(Above)) for the Month of Jan-2021 it is giving the expected Quality Score a sum of points scored over points possible over the last three months, however, for month Feb -2021 its also taking the last three months but taking the Jan - 2021 month which is not what I want. In the case for Feb -2021 where one of the preceding months also returns no data I want it to pull the preceding months where there is data. **Also I don't want to apply this to the current month which I covered that in my if statement with IsCurrentMTD = '1' so that one is fine. Any suggestions would be nice. Thank you.
Dana
| Month-Year | QA | QA^^ | Points Scored | Points Possible |
| Jul-2020 | 99.13% | 99.13% | 573 | 578 |
| Aug-2020 | 98.68% | 98.68% | 673 | 682 |
| Sep-2020 | 98.36% | 98.36% | 480 | 488 |
| Oct-2020 | 97.95% | 97.95% | 383 | 391 |
| Nov-2020 | 98.11% | 98.11% | 467 | 476 |
| Dec-2020 | 100.00% | 100.00% | 372 | 372 |
| Jan-2021 | - | 98.63% | 0 | 0 |
| Feb-2021 | - | 98.94% | 0 | 0 |
| Mar-2021 | 100.00% | 100.00% | 378 | 378 |
| Apr-2021 | - | - | 0 | 0 |
