Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jbakerstull
Contributor

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 IdEntry Exit Provider NameEntry DateExit DateExit Date AdjustedDays in Program
758LSS - FM Faith on 6th3/22/20184/5/20184/5/201814
758LSS - FM Faith on 6th4/26/20186/4/20186/4/201839
2134LSS - FM Faith on 6th3/30/20186/15/20186/15/201877
2517LSS - FM Faith on 6th3/9/20185/9/20185/9/201861
4586LSS - FM Nancy's Place2/10/2018-6/20/2018140
4587LSS - FM Nancy's Place2/19/20185/4/20185/4/201874
5128LSS - FM Nancy's Place5/19/20186/4/20186/4/201816
5129LSS - FM Nancy's Place3/1/20184/11/20184/11/201841
5196LSS - FM Faith on 6th3/6/2018-6/20/2018116
6451LSS - FM Faith on 6th6/5/20186/6/20186/6/20181
14320LSS - FM Faith on 6th4/8/2018-6/20/201883
15950LSS - FM Faith on 6th5/24/2018-6/20/201837
20358LSS - FM Nancy's Place3/7/20185/8/20185/8/201862

Thanks

1 Solution

Accepted Solutions

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]))

2 Replies

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]))

jbakerstull
Contributor

Re: Aggregate Question (not easy)

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

Community Browser