In straight table have rows show only when two conditions are met in one column

[tlegit]

Hello, This is my first post so bare with me. 

I have a requirement to show only clients that have both an Over/Under associated in the Visit Target column like this:

Client	Payor	Plan	Svc	VisitMonth	AuthHours	VisitHours	VisitTarget	Provisional
A	A	A	ADT	04/01/2022	15	10	Under	No
B	C	C	CL B	04/01/2022	20	5	Under	Yes
C	B	B	RE	04/01/2022	10	0	Under	No
C	B	B	HM	04/01/2022	2	2	Even	Yes
E	A	A	CL B	04/01/2022	0	0	Under	Yes
E	A	A	RE	04/01/2022	25	48.15	Over	No
E	A	A	ADT	04/01/2022	1	0	Under	No

All other records need to be ignored if the client does not have a Under AND Over.

How can I go about this? I have tried set analysis, if statements, match, and other functions but I can't figure it out. I have also searched the forum and googled quite a bit, but wasn't able to find a solution. If it helps, from what I can tell based off my data-- the Svc seems to always be distinct for the Over result compared to the other VisitTargets.

[vinieme12]

either of the two will work

sum({<Client=p({<VisitTarget={'Under'}>}Client)>*<Client=p({<VisitTarget={'Over'}>}Client)>}VisitHours)

OR

sum({<Client={"=count({<VisitTarget={'Over','Under'}>}distinct VisitTarget)=2"}>}VisitHours)

[tlegit]

Hi Vinieme12,

Thank you for your response.

I got your expressions to work, but I don't see the data the way I need it to be read. 

From what I understand, the expression you provided only calculates totals if the Visit Target is either Over or Under. However, I need to show the table such as this:

Client	Payor	Plan	Svc	VisitMonth	AuthHours	VisitHours	VisitTarget	Provisional
A	A	A	ADT	04/01/2022	15	10	Under	No
B	C	C	CL B	04/01/2022	20	5	Under	Yes
C	B	B	RE	04/01/2022	10	0	Under	No
C	B	B	HM	04/01/2022	2	2	Even	Yes
E	A	A	CL B	04/01/2022	0	0	Under	Yes
E	A	A	RE	04/01/2022	25	48.15	Over	No
E	A	A	ADT	04/01/2022	1	0	Under	No
D	B	B	RE	04/01/2022	12	15	Over	Yes
D	B	B	ADT	04/01/2022	5	5	Even	No

Where only the clients that have Both an Over and Under show. If the client has an Over, but not an under I don't want it to show and vice versa.

Your previous expression only does this:

Values other then Under/Over are not calculated and return zero, but clients that don't have both an Under/Over still show. Is this even possible?

[vinieme12]

The below expression will work in this case, but the implementation is incorrect in your chart

sum({<Client=p({<VisitTarget={'Under'}>}Client)>*<Client=p({<VisitTarget={'Over'}>}Client)>}VisitHours)

This will return the intersection of two sets

( set1 Clients with VisitTarget as Under

INTERSECT

set2 Client s with VisitTarget as Over )

Please paste the expression that you are using

sum({<Client=p({<VisitTarget={'Under'}>}Client)>*<Client=p({<VisitTarget={'Over'}>}Client)>}VisitHours)

[tlegit]

My Client field is a variable based off of a flag- 

IF(MultAuthFlag=1,Client,NULL())

I tried both of your expressions:

sum({<"$(vOPFClient)"={"=count({<VisitTarget={'Over','Under'}>}distinct VisitTarget)=2"}>}OPFVisitHrs)

sum({<"$(vOPFClient)"={"=p({<VisitTarget={'Under'}>}$(vOPFClient))"}>*<"$(vOPFClient)"={"=p({<VisitTarget={'Over'}>}$(vOPFClient))"}>}OPFVisitHrs)

[vinieme12]

This is incorrect  , p() shouldn't be enclosed in {}

sum({<"$(vOPFClient)"={"=p({<VisitTarget={'Under'}>}$(vOPFClient))"}>*<"$(vOPFClient)"={"=p({<VisitTarget={'Over'}>}$(vOPFClient))"}>}OPFVisitHrs)

remove the brackets, and copy paste the below into your chart

sum({<"$(vOPFClient)"=p({<VisitTarget={'Under'}>}"$(vOPFClient)")>*<"$(vOPFClient)"=p({<VisitTarget={'Over'}>}"$(vOPFClient)")>}OPFVisitHrs)

[vinieme12]

raw:
LOAD
* Inline [
Client,Payor,Plan,Svc,VisitMonth,AuthHours,VisitHours,VisitTarget,Provisional
A,A,A,ADT,04/01/2022,15,10,Under,No
B,C,C,CL B,04/01/2022,20,5,Under,Yes
C,B,B,RE,04/01/2022,10,0,Under,No
C,B,B,HM,04/01/2022,2,2,Even,Yes
E,A,A,CL B,04/01/2022,0,0,Under,Yes
E,A,A,RE,04/01/2022,25,48.15,Over,No
E,A,A,ADT,04/01/2022,1,0,Under,No
DB,B,RE,04/01/2022,12,15,Over,Yes
D,B,B,ADT,04/01/2022,5,5,Even,No
];
exit Script;

cHART

sum({<[$(vOPFClient)]=p({<VisitTarget={'Under'}>}[$(vOPFClient)])>*<[$(vOPFClient)]=p({<VisitTarget={'Over'}>}[$(vOPFClient)])>}OPFVisitHrs)

[tlegit]

I have tried both expressions, I only receive errors and the functions don't return anything:

sum({<[$(vOPFClient)]=p({<VisitTarget={'Under'}>}[$(vOPFClient)])>*<[$(vOPFClient)]=p({<VisitTarget={'Over'}>}[$(vOPFClient)])>}OPFVisitHrs)

sum({<"$(vOPFClient)"=p({<VisitTarget={'Under'}>}"$(vOPFClient)")>*<"$(vOPFClient)"=p({<VisitTarget={'Over'}>}"$(vOPFClient)")>}OPFVisitHrs)

[vinieme12]

The problem is with the variable definition

=IF(MultAuthFlag=1,'Client',NULL())

Add the equal sign in the variable definition and the field name should be as string within single  quotes

[tlegit]

This did not work, why is the field name in quotes? The field Client is a dimension value with many different values, I don't understand that syntax even with the pre-assignment happening on the variable level. 

I believe the issue lies in how the variable is being set up to be a dimension. 

How do you load the if statement variable as a dimension? 

I tried implementing many different ways alongside your suggestion and I can't even get the value to pull in a kpi when I call the variable using either $(vOPFClient) or vOPFClient:

In my straight table if I write the if statement directly then the values pull fine, but if I use the variable nothing pulls. If I can correct that, then I should be able to do the same in the P() function expression you provided.