How to calculate a value based on a subset of data
I am trying to do a calculation and do not know how to approach it. I have tried using the if statement as well as set analysis but am not getting the expected result. I can get the expected result if I manually select values from a list box, but I want the results to reflect on a dashboard, so cannot carry out the process as described below.
To get the results I select all members for a single month who are registered on a disease management programme. I then lock those members. I then unselect the single month that I previously selected. I can now do calculations on the claims for the sub-set of members I have locked over a time period that is not limited to the single period I previously selected. When performing this calculation via set analysis I do no know how to only select the members who qualified in a specific month, and to access their claims that go beyond the month that qualifies them to be in the analysis.
If I was using SQL to extract this information I would write out a temporary table with the members who qualify (the ones I locked in the above example) and then use this temporary table to link to the claims table to get all the claims I want.
How do I achieve the results I have described for SQL in QV?
I have attached a Qv model with 3 patients. On the chart 'Disease Outcomes' the first 2 patients are registered on the HIV programme and have had a CD4 test done. The 3rd patient was registered on the programme, but not in July 2021. I do not want the amount spent on CD4 tests (361) to be added to the total. I only want patients who were registered on the programme in July 2021. For the patients registered on the programme in July 2021 I want all their claims going back to January 2020. In this table I expect to see 2 lines with a total value of 1468 (not 1829)