Pick match aggr challenge part 3!!

adamdavi3s — Thu, 24 Oct 2013 20:20:28 GMT

Part three in the saga leading on from here:

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!

Re: Pick match aggr challenge part 3!!

Thu, 07 Nov 2013 16:20:15 GMT

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

topic Re: Pick match aggr challenge part 3!! in QlikView

Pick match aggr challenge part 3!!

Re: Pick match aggr challenge part 3!!