Set analysis for counting based on < or > average

[Ciara]

Hi All

I want to count each distinct account number where the length of stay (LOS) is greater than the average LOS and another that counts all accounts that have an LOS less than or equal to the average LOS.

I've been trying multiple different syntax of which the below got me the closest to what I need.  I'm displaying this in a PIE chart.  

If(ValueList('Under Avg LOS','Over Avg LOS')='Under Avg LOS',COUNT(DISTINCT{<LOS = {"=LOS <= Avg(LOS)"}>}AccountNumber),
If(ValueList('Under Avg LOS','Over Avg LOS')='Over Avg LOS',COUNT(DISTINCT{<LOS = {"=LOS > Avg(LOS)"}>}AccountNumber)))

Any help is appreciated.  Thanks 

Ciara

[Kushal_Chawda]

Expression you are using giving correct result? If not what is wrong?

[Vegar · Accepted solution]

It could be the avg calculation in the set modifier that is causing you trouble. 

Try this

If(ValueList('Under Avg LOS','Over Avg LOS')='Under Avg LOS',COUNT(DISTINCT{<LOS = {"=LOS <= $(=Avg(LOS))"}>}AccountNumber),
If(ValueList('Under Avg LOS','Over Avg LOS')='Over Avg LOS',COUNT(DISTINCT{<LOS = {"=LOS > $(=Avg(LOS))"}>}AccountNumber)))

[Ciara]

You are a star! Thank you so much Vegar 😁

That worked perfectly.  I'm still trying to get my head around set analysis.