Pick match aggr challenge part 3!!

[adamdavi3s]

Part three in the saga leading on from here:

Pick Match Challenge part 2

How can I accurately create an average cost for SetB in the attached document?

Hopefully the notes in the document make sense, I would type it all here, but honestly it wouldn't make sense!

[#2]

Hello Adam,

I have the same problem will using pick/match: it doesn't respect the set analysis. See if this is what you are looking for in the challenge 2 (maybe it can help in here)...

Create a variable vCostType = match(cost_type,'BLOOD','DRUGS','IMPLANTS','WARDS AND NURSING')

Change your pick to: =pick($(vCostType),

Change the 4 expressions to include:
sum(

{<TRUST={$(TRUSTB)}>}

aggr( SUM({<TRUST={$(TRUSTB)}>}[BLOOD])/If(v_use_mff=1,sum({<TRUST={$(TRUSTB)}>}distinct [MFF]),1),TRUST,cost_type))

Cheers,

José Bagina