Incorrect "presentations contributing to variation" showing on measure
Opened this issue · 6 comments
We had a query from a user re www.openprescribing.net/measure/lpneedles
Could someone explain the rationale as to why BD viva insulin pen needles are in this category.
They are under £5 per 100 at £3.59 for 90.
Apologies if I have misunderstood.
Having taken a look, and the user is correct - the measure should not be showing this,
This measure has a slightly more complicated definition than usual, as it joins to measures.vw__median_price_per_unit on both bnf_code and date.
On first thought I thought that the definition through OP was not working correctly, although the SQL did work correctly in BQ.
Having checked again though, the measure calculation appears to be correct (£575 for last quarter), but the "presentations contributing to variation" does not match, showing a cost of £946.29. If you remove the items from the list that don't cost >=£5 per 100, this comes to the same as the measure (£575).
Therefore it appears to be the way that OP generates the list of BNF codes for presentations contributing to variation, rather than the actual measure itself.
Relevant part of measure definition:
"numerator_type": "custom",
"numerator_columns": [
"SUM(actual_cost) AS numerator"
],
"numerator_from": "{hscic}.normalised_prescribing p LEFT JOIN {measures}.vw__median_price_per_unit r ON p.month=r.date AND p.bnf_code=r.bnf_code ",
"numerator_where": [
"p.bnf_code IN (SELECT bnf_code FROM dmd.vmp WHERE LOWER(nm) LIKE '%insulin needles%' AND bnf_code IS NOT NULL -- find vmp products with vmp name containing insulin needles \n",
"UNION DISTINCT ",
"SELECT amp.bnf_code FROM dmd.amp AS amp INNER JOIN dmd.vmp ON vmp.id = amp.vmp WHERE LOWER(vmp.nm) LIKE '%insulin needles%' AND amp.bnf_code IS NOT NULL) -- find amp products with vmp name containing insulin needles \n",
"AND r.median_price_per_unit >= 0.05 --this selects products with median price of >= 5 pence per needles same as >= 5 pound per 100 needles "
],Is there a way to force the presentation BNF codes to be correct?
I suppose we could fix by using a view in BQ rather than SQL in the measure definition, but i'm not keen on the resulting lack of transparency on how the measure is built
OK @inglesp some more information:
For the 3 months ending September 2023 the practice has prescribed 17 different items:
| bnf_name | bnf_code | items |
|---|---|---|
| BD AutoShield Duo hypod insulin needles for pre-filled / reusable pen injectors screw on 5mm/30gauge | 21010900606 | 1 |
| GreenFine hypodermic insulin needles for pre-filled / reusable pen injectors screw on 4mm/32gauge | 21010900928 | 1 |
| GlucoRx CarePoint hypod insulin needles for pre-filled / reusable pen injectors screw on 4mm/31gauge | 21010900703 | 1 |
| Unifine Pentips hypod insulin needles for pre-filled / reusable pen injectors screw on 4mm/32gauge | 21010900713 | 2 |
| Microdot Dual Safety Pen hypod insulin needles for pf / reusable pen injectors screw on 4mm/31gauge | 21010900956 | 2 |
| NovoFine hypodermic insulin needles for pre-filled / reusable pen injectors screw on 8mm/30gauge | 21010900706 | 3 |
| GlucoRx FinePoint hypod insulin needles for pre-filled / reusable pen injectors screw on 4mm/31gauge | 21010900802 | 3 |
| Neon Verifine Safety hypod ins needles for pre-filled / reusable pen injectors screw on 8mm/30gauge | 21010900899 | 3 |
| BD Viva hypodermic insulin needles for pre-filled / reusable pen injectors screw on 5mm/31gauge | 21010900608 | 5 |
| BD Micro-Fine Ultra hypod insulin needles for pre-filled/reusable pen injectors screw on 4mm/32gauge | 21010900603 | 9 |
| NovoFine Autocover hypod insulin needles for pre-filled /reusable pen injectors screw on 8mm/30gauge | 21010900708 | 15 |
| Neon Verifine hypodermic insulin needles for pre-filled /reusable pen injectors screw on 5mm/31gauge | 21010900884 | 18 |
| Neon Verifine hypodermic insulin needles for pre-filled /reusable pen injectors screw on 8mm/31gauge | 21010900886 | 18 |
| Neon Verifine hypodermic insulin needles for pre-filled /reusable pen injectors screw on 6mm/31gauge | 21010900885 | 28 |
| Neon Verifine Ultra hypod insulin needles for pre-filled/reusable pen injectors screw on 4mm/32gauge | 21010900889 | 61 |
| BD Viva hypodermic insulin needles for pre-filled / reusable pen injectors screw on 4mm/32gauge | 21010900607 | 67 |
| Neon Verifine hypodermic insulin needles for pre-filled /reusable pen injectors screw on 4mm/31gauge | 21010900883 | 90 |
You can take these BNF codes and see which of them were showing as >£5/100, and when, using this SQL:
SELECT max(date(date)) as latest_month_high, bnf_code, presentation FROM `ebmdatalab.measures.vw__median_price_per_unit` as rx
inner join hscic.bnf as bnf
on bnf.presentation_code = rx.bnf_code
where bnf_code IN ('21010900606',
'21010900928',
'21010900703',
'21010900713',
'21010900956',
'21010900706',
'21010900802',
'21010900899',
'21010900608',
'21010900603',
'21010900708',
'21010900884',
'21010900886',
'21010900885',
'21010900889',
'21010900607',
'21010900883')
and median_price_per_unit >.05
group by bnf_code, presentation
order by bnf_codeThis gives the following items:
| latest_month_high | bnf_code | presentation |
|---|---|---|
| 01/11/2020 | 21010900603 | BD Micro-Fine Ultra hypod insulin needles for pre-filled/reusable pen injectors screw on 4mm/32gauge |
| 01/09/2023 | 21010900606 | BD AutoShield Duo hypod insulin needles for pre-filled / reusable pen injectors screw on 5mm/30gauge |
| 01/06/2017 | 21010900607 | BD Viva hypodermic insulin needles for pre-filled / reusable pen injectors screw on 4mm/32gauge |
| 01/06/2017 | 21010900608 | BD Viva hypodermic insulin needles for pre-filled / reusable pen injectors screw on 5mm/31gauge |
| 01/08/2017 | 21010900703 | GlucoRx CarePoint hypod insulin needles for pre-filled / reusable pen injectors screw on 4mm/31gauge |
| 01/09/2023 | 21010900706 | NovoFine hypodermic insulin needles for pre-filled / reusable pen injectors screw on 8mm/30gauge |
| 01/09/2023 | 21010900708 | NovoFine Autocover hypod insulin needles for pre-filled /reusable pen injectors screw on 8mm/30gauge |
| 01/09/2023 | 21010900713 | Unifine Pentips hypod insulin needles for pre-filled / reusable pen injectors screw on 4mm/32gauge |
| 01/03/2019 | 21010900802 | GlucoRx FinePoint hypod insulin needles for pre-filled / reusable pen injectors screw on 4mm/31gauge |
| 01/09/2023 | 21010900899 | Neon Verifine Safety hypod ins needles for pre-filled / reusable pen injectors screw on 8mm/30gauge |
| 01/09/2023 | 21010900956 | Microdot Dual Safety Pen hypod insulin needles for pf / reusable pen injectors screw on 4mm/31gauge |
Which is identical to the list generated on the "presentations contributing to variation", even though some of the needles haven't had a PPU of >0.05 since 2017.
We can see therefore that it appears that a) the variation list isn't all needles, but b) is all needles that show on the "high" list at any point, not just currently.
I think we can fix this by providing a numerator_bnf_codes_query. An example of us doing this is in lpherbal.json.
Can you give this a go?
@inglesp how do I factor the date into this?
@inglesp have deployed with following SQL for numerator_bnf_codes_query
SELECT DISTINCT p.bnf_code
FROM hscic.normalised_prescribing p
LEFT JOIN measures.vw__median_price_per_unit r ON p.month=r.date AND p.bnf_code=r.bnf_code
WHERE
p.bnf_code IN
(SELECT bnf_code FROM dmd.vmp WHERE LOWER(nm) LIKE '%insulin needles%' AND bnf_code IS NOT NULL -- find vmp products with vmp name containing insulin needles \n",
UNION DISTINCT
SELECT amp.bnf_code FROM dmd.amp AS amp INNER JOIN dmd.vmp ON vmp.id = amp.vmp WHERE LOWER(vmp.nm) LIKE '%insulin needles%' AND amp.bnf_code IS NOT NULL) -- find amp products with vmp name containing insulin needles \n",
AND r.median_price_per_unit >= 0.05 --this selects products with median price of >= 5 pence per needles same as >= 5 pound per 100 needles Same problem exists. Unless we can link date somehow, not sure how to fix.
if we added in month there would that help?