Set Analysis - Penetration

[#1]

Hi Community,

I am attempting a product penetration calculation to accompany a map.  What I would like to happen is when I select a particular state, to have the industry penetration for just that state populate.  Long story short, is there a way to add a modifier to the below expression to only calculate based on the state selected.  Its currently dividing industry within the state by total industry across the country.

count(distinct {<Lost={'N'},TotalActiveEmployeesClient=-{'=Len(Trim(TotalActiveEmployeesClient))=0'}>}[ClientNumber])/count(distinct {1<Lost={'N'},TotalActiveEmployeesClient=-{'=Len(Trim(TotalActiveEmployeesClient))=0'}>}[ClientNumber])

Hopefully that makes sense.

Thanks

TJ

[swuehl]

Maybe like

count(distinct {<Lost={'N'},TotalActiveEmployeesClient=-{'=Len(Trim(TotalActiveEmployeesClient))=0'}>}[ClientNumber])

/count(distinct {1<Lost={'N'},TotalActiveEmployeesClient=-{'=Len(Trim(TotalActiveEmployeesClient))=0'}, Industry = p()>}[ClientNumber])

[sunny_talwar]

Stefan -

I might be wrong, but shouldn't it be this:

count(distinct {<Lost={'N'},TotalActiveEmployeesClient=-{'=Len(Trim(TotalActiveEmployeesClient))=0'}>}[ClientNumber])/count(distinct {1<Lost={'N'},TotalActiveEmployeesClient=-{'=Len(Trim(TotalActiveEmployeesClient))=0'}, State = p(State)>}[ClientNumber])

[swuehl · Accepted solution]

I am not 100% sure, as I've read above statement, it's the Industry that needs to be modified.

But p() function should / could be the way to go here.

[#5]

I was trying to keep the base state static.  This function worked perfectly...

count(distinct {<Lost={'N'},TotalActiveEmployeesClient=-{'=Len(Trim(TotalActiveEmployeesClient))=0'}>}[ClientNumber])/

count(distinct {1<Lost={'N'},TotalActiveEmployeesClient=-{'=Len(Trim(TotalActiveEmployeesClient))=0'},State = p()>}[ClientNumber])

Thank you to both of you!