Negative results

[jbakerstull]

The formula below returns the number of days that are within a reporting period. The formula captures all persons.

Sum(Num(IF([Exit Date Adjusted]>'3/31/2019','3/31/2019',[Exit Date]))-
Num(IF([Entry Date]<'01/01/2019','01/01/2019',[Entry Date])))

If my data set, data element [Household Head of Household] identifies head of households by [Household Head of Household]='Yes'

My understanding is that I'm unable to use a set expression because of use of If statements. 

I'm trying to incorporate [Household Head of Household]='Yes' data element into formula as below.

Sum(Num(IF([Exit Date Adjusted]>'3/31/2019','3/31/2019',[Exit Date]))-
Num(IF([Entry Date]<'01/01/2019','01/01/2019',[Entry Date]))
And[Household Head of Household]='Yes')

The result is negative days. My goal is to count number of days for [Household Head of Household]='Yes'. 

Thans

[jbakerstull]

the formula below returns the same result but just in a set expression

=Sum({$<[Entry Date]={'<=$(=EndDate)'},[Exit Date Adjusted]={'>=$(=StartDate)'}>}[Household Head of Household]='Yes')

[JordyWegman]

Hi,

I think you should count the days. But in your formula, you are missing the thing that you are counting/summing. In the part I've added the household to the set analysis.  Fill in WhatDoYouWantToCount the field that you want to count.

Count({$<[Entry Date]={'<=$(=EndDate)'},[Exit Date Adjusted]={'>=$(=StartDate)'}, [Household Head of Household]= {'Yes'} >} [WhatDoYouWantToCount])

Jordy

Climber

[jbakerstull]

I don't work in Qlik everyday, I'm self teaching myself. Which is hard because I can just ask someone for guidance. Does  the set expression always have to include a data element at the end to return a value ? 

Formula below is what I'm using to sum all days within report period. 

Sum(Num(IF([Exit Date Adjusted]>[EndDate],[EndDate],Date([Exit Date]))-
Num(IF([Entry Date]<StartDate,StartDate,Date([Entry Date])))))

I tried the formula and the result return an 11 when using [Entry Date]. The total number of shelter units is 383

Client Id	Shelter Units	Entry Date	Exit Date	Household Head of Household
8512	89	10/9/2018	4/26/2019	Yes
50250	29	3/2/2019	5/6/2019	Yes
60180	44	2/15/2019	4/3/2019	Yes
63428	0	3/31/2019	4/4/2019	Yes
70581	38	2/15/2019	3/25/2019	Yes
70581	2	3/29/2019	5/28/2019	Yes
100999	83	1/7/2019	4/29/2019	Yes
121531	12	8/12/2018	1/13/2019	Yes
130375	2	2/1/2019	2/3/2019	Yes
148816	76	1/14/2019	5/1/2019	Yes
167203	8	2/13/2019	2/21/2019	Yes

I'm thinking that formula below needs to converted into a variable in order to count total shelter units. 

Sum(Num(IF([Exit Date Adjusted]>[EndDate],[EndDate],Date([Exit Date]))-
Num(IF([Entry Date]<StartDate,StartDate,Date([Entry Date])))))