2 Replies Latest reply: Jun 21, 2018 3:46 PM by Jeremiah Bakerstull

Aggregate Question (not easy)

Good afternoon,

Below formula calucates the total number of days a person resides in a program during a service.

=If(substringcount([Exit Date],'.')=0,Floor(Num([Exit Date Adjusted])-[Entry Date]),Floor(Num('6/30/2018'-[Entry Date])))

Is it possible to aggregate total number of days in a KPI?

I only have front in abilities.

Sample Data

 Client Id Entry Exit Provider Name Entry Date Exit Date Exit Date Adjusted Days in Program 758 LSS - FM Faith on 6th 3/22/2018 4/5/2018 4/5/2018 14 758 LSS - FM Faith on 6th 4/26/2018 6/4/2018 6/4/2018 39 2134 LSS - FM Faith on 6th 3/30/2018 6/15/2018 6/15/2018 77 2517 LSS - FM Faith on 6th 3/9/2018 5/9/2018 5/9/2018 61 4586 LSS - FM Nancy's Place 2/10/2018 - 6/20/2018 140 4587 LSS - FM Nancy's Place 2/19/2018 5/4/2018 5/4/2018 74 5128 LSS - FM Nancy's Place 5/19/2018 6/4/2018 6/4/2018 16 5129 LSS - FM Nancy's Place 3/1/2018 4/11/2018 4/11/2018 41 5196 LSS - FM Faith on 6th 3/6/2018 - 6/20/2018 116 6451 LSS - FM Faith on 6th 6/5/2018 6/6/2018 6/6/2018 1 14320 LSS - FM Faith on 6th 4/8/2018 - 6/20/2018 83 15950 LSS - FM Faith on 6th 5/24/2018 - 6/20/2018 37 20358 LSS - FM Nancy's Place 3/7/2018 5/8/2018 5/8/2018 62

Thanks

• Re: Aggregate Question (not easy)

May be this

Sum(Aggr(If(substringcount([Exit Date],'.') = 0, Floor(Num([Exit Date Adjusted]) - [Entry Date]), Floor(Num('6/30/2018' - [Entry Date]))), [Client Id], [Entry Exit Provider Name], [Entry Date], [Exit Date Adjusted]))

• Re: Aggregate Question (not easy)

If I could you send you a thank you card in the mail with chocolate, I would