Hello,
I’m trying to create a set analysis equation to count the amount of clients that have income between various ranges (see below for specific ranges.) The income, for the most part, contains historical data but has fields to indicate if the income has been received, when it started, when it ended, and the type of income it was.
The equation also needs to have a way of controlling a user inputted “Report Start” and “Report End”.
The field “New End Date” identifies nulls that appeared within “JoinIncome.Income End Date” by assigning -1.
The Field “NewIncome2” is the following equation:
if([JointIncome.Receiving Income Source] = 'Yes', [JointIncome.Monthly Sum]- ([JointIncome.Monthly Sum]-[JointIncome.Monthly Amount]))
The field “NewIncomeSum” =
if([JointIncome.Receiving Income Source] = 'No', [JointIncome.Monthly Sum] - [JointIncome.Monthly Amount])
Ranges: 1-150, 151 – 250, 251-500, 501 – 1000, 1001-1500, 1501-2000, 2001+
Work so far for range 251-500 that should yield 30 clients:
Count({
//Select in the report range
($<[Entry Exit Entry Date]={"<=$(=ReportEnd)"},[Entry Exit Has No Exit Date]={'Yes'}>
+$<[Entry Exit Entry Date]={"<=$(=ReportEnd)"},[Entry Exit Exit Date]={">=$(=ReportStart)"}>)
*
($<[Current Age]={">=18"},[Income Start Date]={"<=$(=Entry Exit Entry Date)"},[Income End Date]={">=$(=Entry Exit Entry Date)"},[Monthly Income Sum]={">250<=500"}>
+$<[Current Age]={">=18"},[Income Start Date]={"<=$(=Entry Exit Entry Date)"},[New End Date]={'-1'},[Monthly Income Sum]={">250<=500"}>)
+
(
($<[Current Age]={">=18"},[Income Start Date]={"<=$(=Entry Exit Entry Date)"},[Income End Date]={">=$(=Entry Exit Entry Date)"},[NewIncome2]={">250<=500"}>
+$<[Current Age]={">=18"},[Income Start Date]={"<=$(=Entry Exit Entry Date)"},[New End Date]={'-1'},[NewIncome2]={">250<=500"}>)
*
($<[Current Age]={">=18"},[Income Start Date]={"<=$(=Entry Exit Entry Date)"},[Income End Date]={">=$(=Entry Exit Entry Date)"},[NewIncomeSum]={">250<=500"}>
+$<[Current Age]={">=18"},[Income Start Date]={"<=$(=Entry Exit Entry Date)"},[New End Date]={'-1'},[NewIncomeSum]={">250<=500"}>)
)
*
(
($<[Current Age]={">=18"},[Income Start Date]={"<=$(=Entry Exit Entry Date)"},[Income End Date]={">=$(=Entry Exit Entry Date)"},[NewIncome2]={">250<=500"}>
+$<[Current Age]={">=18"},[Income Start Date]={"<=$(=Entry Exit Entry Date)"},[New End Date]={'-1'},[NewIncome2]={">250<=500"}>)
)
}
Distinct([Entry Exit Uid]))
