Excluding Selection from Set Analysis

[Deputy_Dawg]

Hi All, 

I'm working with hospital data and looking at hospital charges for various services. 

Some hospital charges are way out of the ball park and that is easy to see when I compare them to the average of ALL the hospitals.

However, in some circumstances I think the difference would be bigger and more obvious if I exclude the selected hospital that is being compared. That is I would like to compare it to all the OTHER hospitals. 

So I derived the following RATIO after looking within the forums here:

Avg(${<DRVD_HSPTL_CHRG_AMT={">0"}>}DRVD_HSPTL_CHRG_AMT)/
Avg({1<DRVD_HSPTL_CHRG_AMT={">0"},DRVD_HSPTL_NM-={"GetFieldSelections([DRVD_HSPTL_NM])"}>}DRVD_HSPTL_CHRG_AMT)

Note that there is a tiny minus sign in there near the end of the 2nd line.

I was hoping this would work when I select a hospital in the hospital filter but the results aren't even close to what I find with SQL in our data warehouse.

Is the syntax correct here ? My data model is good and returns expected results for everything else.  I'm just going in circles now. Any help would be appreciated.

Thank you !!

[sandeep-singh]

I made a small change in your expression. Please check now

Avg({<DRVD_HSPTL_CHRG_AMT={">0"}>} DRVD_HSPTL_CHRG_AMT) /
Avg({<DRVD_HSPTL_CHRG_AMT={">0"}, DRVD_HSPTL_NM -= {"$(=GetFieldSelections([DRVD_HSPTL_NM]))"}>} DRVD_HSPTL_CHRG_AMT)

[Deputy_Dawg]

Hi Sandeep,

Thanks for giving that a go… the denominator now becomes a single hyphen “ –  “ and doesn’t produce a numerical value.

I did see that dollar sign syntax and double quotes before and have been hacking away at it in the vain hope I might somehow jag the right answer.

Interestingly, and I should have mentioned this before, the syntax highlight for "$(=GetFieldSelections([DRVD_HSPTL_NM]))" is green but the equation assessment is “OK”

When I just use {GetFieldSelections([DRVD_HSPTL_NM]))"} the GetFieldSelections keyword is blue and the Hospital Name DRVD_HSPTL_NM is brown/beige.

That looks good but the equation assessment is Error and it doesn’t produce a result.

I would have thought this would be a very common type of expression to use in Set Analysis but it is hard to find examples.

[RafaelBarrios]

Hi @Deputy_Dawg

my guess is that [DRVD_HSPTL_NM] is a text field, so the problem is that you need the result of "GetFieldSelections([DRVD_HSPTL_NM])" to be something like {'name1','name2',.....,'namen'} in order to work as you need.

Try this instead 

Avg(1-${<DRVD_HSPTL_CHRG_AMT={">0"}>}DRVD_HSPTL_CHRG_AMT)

1 = your complete set of data

$ = your selected set of data

1-$ = the excluded data from your selection

hope this helps.

Best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!  😁

[Deputy_Dawg]

Hi Rafael, thank you for taking the time to have a go at this.

Unfortunately I don't get the expect answer with your solution. 

For a particular hospital, the average charge is $4,130

The average for all other hospitals is $3,387 (calculated with SQL). This is what I'm having trouble calculating, the denominator for the ratio of averages. 

Your solution produces -$4129 which is very similar to the numerator. 

I like your logic, maybe something similar will help me avoid using the GetFieldSelections which doesn't want to work for me (sob).

Thanks again !

[vinieme12 · Accepted solution]

try below

Avg(${<DRVD_HSPTL_CHRG_AMT={">0"}>}DRVD_HSPTL_CHRG_AMT)/
Avg({1<DRVD_HSPTL_CHRG_AMT={">0"},DRVD_HSPTL_NM=e([DRVD_HSPTL_NM])>}DRVD_HSPTL_CHRG_AMT)

or

Avg(${<DRVD_HSPTL_CHRG_AMT={">0"}>}DRVD_HSPTL_CHRG_AMT)/
Avg({1<DRVD_HSPTL_CHRG_AMT={">0"},DRVD_HSPTL_NM=e([DRVD_HSPTL_NM])>}total DRVD_HSPTL_CHRG_AMT)

[Deputy_Dawg]

Hi Sandeep,
Thanks for giving that a go... the denominator now becomes a single hyphen " - " and doesn't produce a numerical value.
I did see that dollar sign syntax and double quotes before and have been hacking away at it in the vain hope I might somehow jag the right answer.
Interestingly, and I should have mentioned this before, the syntax highlight for "$(=GetFieldSelections([DRVD_HSPTL_NM]))" is green but the equation assessment is "OK"
When I just use {GetFieldSelections([DRVD_HSPTL_NM]))"} the GetFieldSelections keyword is blue and the Hospital Name DRVD_HSPTL_NM is brown/beige.
That looks good but the equation assessment is Error and it doesn't produce a result.
I would have thought this would be a very common type of expression to use in Set Analysis but it is hard to find examples.

[Deputy_Dawg · Accepted solution]

Wow ! Thank you so much Vineeth. This was very close, just needed to replace the '1' with a '$' as follows:

Avg({$<DRVD_HSPTL_CHRG_AMT={">0"},DRVD_HSPTL_NM=e([DRVD_HSPTL_NM])>}DRVD_HSPTL_CHRG_AMT)

Now it matches exactly what our teradata sql specialist calculated the target to be.

I have no idea why this particular logic yields the correct result and I should be ashamed for not understanding why but I'm gonna take it and run with it ! That's a log jam broken, I'm back in business : )

Thanks to everyone involved in helping me find this solution. This is a great community !!!

[Kushal_Chawda]

@Deputy_Dawg  you should also mark @vinieme12 's answer correct as it helped you to arrive at solution. Except just a sign it is exactly what you need. You should give credit to him. Just a thought.

[Deputy_Dawg]

Yes indeed. I did click on Accept as Solution for his answer - credit where credit is due. I would never have worked it out on my own.
This is the first time I've posted so I might not have done it properly. I've re-clicked Accept as Solution and I think it is marked as such now.
Thank you for the tip !