Script Logic

[tmumaw]

Hi Group,

I have a question for all of you.  I need to get some statistics based on the following:

• What % of ServLoc have 4+ MG_Key  proposed?
• What % of ServLoc have only MG_Key = 002 proposed?
• What % of ServLoc have only MG_Key = 004 proposed?
• What % of ServLoc have only MG_Key = 010 proposed?
• What % of ServLoc have MG_Key all (002, 004, 010) proposed?

Here is my table:

Oppt_Summ:
Load
ServiceLocation as ServLoc,
ContractStart_Year as Cont_Year,
Left([Material Grp],3) as MG_Key,
[Material Grp] as MGrp,
NetSales as Net_Sales
Resident Opportunity;   

Any ideas?

Thanks

[YoussefBelloum]

hi,

try to share sample data

[shraddha_g]

Try Below:

1. Count({<ServLoc = { Count(MG_Key)>=4 }>}ServLoc)/Count(Total ServLoc)

2. Count({<MG_Key = { '002' }>}ServLoc)/Count(Total ServLoc)

3.Count({<MG_Key = { '004' }>}ServLoc)/Count(Total ServLoc)

4.Count({<MG_Key = { '004','002','010' }>}ServLoc)/Count(Total ServLoc)

Else Share sample data

[tmumaw]

Pretty simple data.

ServLoc   Cont_Year   MG_Key   MGrp   Net Sales

123              2018             002        Pest         25.00

123              2018             004        Fert        125.00

123              2018             010        Lawn      125.00

456              2018             002        Pest          50.00

456              2018             010        Lawn       175.00

789              2018             004        Fert          35.00

789              2018             010        Lawn       165.00

025              2018             010        Lawn       195.00

[tmumaw]

This would work however if you look at the first post the word "Only" comes into play.

[YoussefBelloum]

find attached inspiration for your first point:

!

[tmumaw]

I don't think it's quite right. There are 26 servloc and 10 have 4 or more MG_Key

[YoussefBelloum]

Ok, please re attach the app with complete data of the 26 servloc having with 10 of them having 4 or more MG_Key

[rubenmarin]

Hi, maybe with a distinct count?:

=count({<ServLoc={"=count(Distinct {<ServLoc>}MG_Key)>=4"}>}ServLoc)/Count(TOTAL ServLoc)

[tmumaw]

Thanks Ruben that was it.