Pick Match Challenge part 2

[adamdavi3s]

Following on from this (which was embarrassingly easy to answer)

http://community.qlik.com/thread/96589

We need to add in a floating dimension to compare two sets of data, based on the same dimension.

This works brilliantly most areas but I can't seem to get the set analysis working within our pick match formula.

Any advice is greatly received, but note that I HAVE to do this this way due to performance issues with other formula.

[vikasmahajan]

I think you should try to implement Comprehensive  Analysis for Comparing One Dimension with Set A & Set B.

Vikas

[adamdavi3s]

Hi Vikas,

I'm actually researching developments for the next release in April next year so any info you have on alternative ideas is greatly received (Qliktech certainly never mentioned this)

However for the mean time I need to find a working solution to this issue so we can release a fix for the current release.

Kind regards,

Adam

[adamdavi3s]

I'm coming up a complete blank with how to fix this

[adamdavi3s · Accepted solution]

This appears to work......

[#6]

Adam,

I have the same problem with using pick/match: don't respect the set analysis. See if this is what you are looking for...

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