Incorrect "presentations contributing to variation" showing on measure

[richiecroker]

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?

[richiecroker]

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

[richiecroker]

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_code

This 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.

[inglesp]

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?

[richiecroker]

@inglesp how do I factor the date into this?

[richiecroker]

@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.

[richiecroker]

if we added in month there would that help?