Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to count a specific value where the "Exit Household value equals to yes.
In the formula below, I'm trying to show in a KPI, "Emergency shelter, including hotel or motel paid for with emergency shelter voucher (HUD)" value of 7. The formula below returns a zero value.
Formula
=Sum(Aggr(If([Exit Date] = Max(TOTAL <[Client Id]> Floor(Num([Exit Date Adjusted])))
and Max(TOTAL <[Client Id]> Floor(Num([Entry Date]))) = [Entry Date] And
if([Exit Destination]='Emergency shelter, including hotel or motel paid for with emergency shelter voucher (HUD)')
, 1, 0), [Client Id], [Exit Date],[Exit Destination]))
Pivot Table
Exit Destination | Exited Household | Yes | No |
Emergency shelter, including hotel or motel paid for with emergency shelter voucher (HUD) | 7 | - | |
Hospital or other residential non-psychiatric medical facility (HUD) | 2 | - | |
Hotel or motel paid for without emergency shelter voucher (HUD) | 1 | - | |
No exit interview completed (HUD) | 49 | 5 | |
Other (HUD) - | 2 | - | |
Permanent housing (other than RRH) for formerly homeless persons (HUD) | 4 | - | |
Place not meant for habitation (HUD) | 84 | 6 | |
Psychiatric hospital or other psychiatric facility (HUD) | 1 | - | |
Rental by client, no ongoing housing subsidy (HUD) | 57 | - | |
Rental by client, with other ongoing housing subsidy (HUD) | 1 | - | |
Rental by client, with RRH or equivalent subsidy (HUD) | 5 | - | |
Staying or living with family, permanent tenure (HUD) | 2 | - | |
Staying or living with family, temporary tenure (e.g., room, apartment or house)(HUD) | 24 | - | |
Staying or living with friends, temporary tenure (e.g., room apartment or house)(HUD) | 9 | 1 | |
Transitional housing for homeless persons (including homeless youth) (HUD) | 1 | - |
I figured it out.
=Sum(Aggr(If([Exit Date] = Max(TOTAL <[Client Id]> Floor(Num([Exit Date Adjusted])))
and Max(TOTAL <[Client Id]> Floor(Num([Entry Date]))) = [Entry Date] And
[Exit Destination]='Emergency shelter, including hotel or motel paid for with emergency shelter voucher (HUD)'
, 1, 0), [Client Id], [Exit Date],[Exit Destination]))
I figured it out.
=Sum(Aggr(If([Exit Date] = Max(TOTAL <[Client Id]> Floor(Num([Exit Date Adjusted])))
and Max(TOTAL <[Client Id]> Floor(Num([Entry Date]))) = [Entry Date] And
[Exit Destination]='Emergency shelter, including hotel or motel paid for with emergency shelter voucher (HUD)'
, 1, 0), [Client Id], [Exit Date],[Exit Destination]))