Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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 |
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 |
|
||||||||
|
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?
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)
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)
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)
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)
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)
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
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.