Min(date) since Max(date(

Bell Frazier shared this question 14 months ago
Answered

Hi,


I have two columns, Intake Date and Treatment Plan Start date. I want the report to display the first (min) Treatment Plan Start Date since the last (max( Intake Date

Replies (11)

photo
1

Hello Bell Frazier,

Thank you for reaching out to the Yellowfin Support Team.

While we are investigating this issue, could you please let us know the exact version and build of Yellowfin you are currently using so that we can replicate the same and get back to you with the findings.

Regards,

Yamini Naidu

photo
1

I’m sorry. I’m trying to find the version but was unsuccessful. I know we tried to upgrade to v9.7 but we had to rollback because of some errors. I’m using Yellowing through Credible Behavior Health, if that helps.

photo
1

Hello Bell Frazier,

Can you please provide us the screen recording of the steps you are performing so that we can replicate the same and get back to you with the findings.

This will help us in better understanding the situation.

Regards,

Yamini Naidu

photo
1

Unfortunately, I cannot. It would be a HIPAA violation.

photo
1

Hello Bell Frazier,

Based on your situation, I believe we can use free hand sql in calculated field.
You can test calculated field based on your conditions to see if it works.

Let me know if that was helpful.

Regards,
Yamini Naidu

photo
photo
1

Are you going to tell me what that code should be?

photo
1

Hello Bell Frazier,

Sorry for the delayed response.

I've included a video that demonstrates how to create a report using calculated field with simple formula that could work for your scenario.

I believe this will be useful to you in some way.

Please go through the video and let me know if I'm not understanding something.

Regards,

Yamini Naidu

photo
1

Let me try to explain a little better. I have a list of clients. Each clients has and intake date and some have multiple intake dates, if they they leave a come back. Each clients also has treatment plans, each with a start date. I would like to know the date of the first treatment plan since their last intake date. Basically, I need the statement to be:


Having (Min(treatmentplan_date)) >= (Max(intake_date))


But I cannot get this to work in Yellowfin

photo
1

You may be able to do this in the view using a virtual table.

--vt_LastIntake_FirstTxPlanDate

SELECT client_id
, max(cv.rev_timein) last_intake
, firstPlan_Date = (

SELECT min(tx_start_date)
FROM Tx_Plans tp
WHERE tp.client_id = cv.client_id
AND tp.tx_start_date > max(cv.rev_timein)
GROUP BY tp.client_id
)
FROM clientvisit cv
WHERE cv.visittype_id = 100 /*visit type id for intake*/
GROUP BY cv.client_id

photo
1

Thank you!

photo
1

Hello Bell Frazier,

Hope you are doing well

You can try Jim Staple's suggestion and let us know if it works for you or not.

@Jim Staples, Thanks for responding to this.

Regards,

Yamini Naidu

photo
1

It worked! Just trying to figure out how to add the other columns as well. Thank you!

photo
1

use the append subquery joining on client_id and date to max-date. May need 2 appends--one for each table depending on the addtional data you are looking for

photo
photo
1

Hello Bell Frazier,

I'm glad to hear that.

If that clarified your concern, could we please close the ticket?

Let us know

Regards,

Yamini Naidu

photo
1

Yes. Thank you

photo
1

Hello Bell Frazier,

Thanks for the confirmation.

I'll go ahead and mark this ticket as answered.

Kind Regard

Yamini Naidu

Leave a Comment
 
Attach a file