Using set analysis to get rid of selections

[#1]

Hi Guys,

I am looking to solve the following problem where I need to get rid of selection on a particular field and get all ids in the system for some specific condition.

Consider ids MCOContractedCustId1, 2, 3 and 4 are connected to volumes 100, 200, 300 and 400 respectively. Once a selection is made on MCOContractedCustId3, Volume obviously get filtered to 300. However, I would like to get all the customers (preferably concatenated) where Volume >= 100, which should give MCOContractedCustId1, 2, 3 and 4 as a result.

Please find attached a prototype. Any help will be appreciated.

Thanks,

Puneet.

[sushil353]

Hi Punit,

Please find updated file

Hope that helps you

Sushil

[#3]

Hi Sushil,

Thanks for the response. I am trying to set an action on a button click for which the MCOContractedCustId(s) need to be concatenated, something like the following -

=Concat( Distinct {1<MCOContractedCustId = {"=Volume>=100"}>} MCOContractedCustId, '|')

However, since MCOContractedCustId = 3 is selected, the only value that remains in the volume field is 300, and therefore, the above expression returns MCOContractedCustId3. However, in the application, all the customers i.e. MCOContractedCustId1, MCOContractedCustId2, MCOContractedCustId3 and MCOContractedCustId4 have volume >= 100. However, since Volume is used in the advanced search above, just the possible volume value 300 is considered.

Is there a way to get a list of customerids which ignore the MCOContractedCustId = 3 selection and find all customers with volume >= 100 ?

Thanks,

Puneet.

[sushil353]

Hi puneet,

try this:

=Concat( Distinct {1<Volume= {">=100"}>} MCOContractedCustId, '|')

HTH

sushil

[#5]

Hi Sushil,

Thanks much for the above answer as it helps a lot. Now, if we want to take a step further and look for the customers with sum(volume)/sum(volumegoal) >=80%, something like an advanced search, would we still get the correct answer when some other customers have been selected ? I was trying out the following expression:

=Concat( Distinct {1<MCOContractedCustId={"=Sum(Volume)/Sum(VolumeGoal)>=0.80"}>} MCOContractedCustId, '|')

Here's the problem statement -

"Problem Description: In this problem, MCOContractedCustId1, 2, 3 and 4 are connected to volumes and volume goals. Once a selection is made on MCOContractedCustId1 and MCOContractedCustId2, Volume and VolumeGoal are obvsiouly filtered such that for MCOCustomerId1, sum(volume)/sum(volumegoal) = 75% and for MCOCustomerId2, sum(volume)/sum(volumegoal) = 76.67%. With MCOContractedCustId1 and MCOContractedCustId2 selected, is it now possible to get customers where Volume/Goal >= 80%, which should give 3|4 as a result."

Please find attached the file.

[sushil353 · Accepted solution]

Hi Puneet,

I did some modification in your script.

Please find the attached file.

HTH

Sushil

[#7]

Thanks Sushil for the help !