How can you use set analysis to count the number of items with a date that is between two other dates

[asheppardwork]

I have a bit of a strange problem; I admit I'm not the best at Set Analysis syntax in Qlik. I am trying to get a count of records where the return_date is between the trigger_date and trigger_date + 30/60/90/180/365/730 days.
I have tried the following formulas to no avail; it either miscounts the 30 days, or doesn't count them, or if it does when I change it to a larger value like 365 days it doesn't count them at all, what am I doing wrong here?

I did try this solution but it does not work for me.

Attempt 1:

=Count({<[Return_Dt.autoCalendar.Date]={">=$([Trigger_Dt.autoCalendar.Date]) <=$([Trigger_Dt.autoCalendar.Date]+30)"}>} Distinct RecordNum)

Attempt 2:

=Count({<Return_Dt.autoCalendar.Date={">=$(=Trigger_Dt.autoCalendar.Date) <=$(=Trigger_Dt.autoCalendar.Date+30)"}>} Distinct RecordNum)

Attempt 3:

=Count({< [Return_Dt.autoCalendar.Date]= {">=([Trigger_Dt.autoCalendar.Date])  <=([Trigger_Dt.autoCalendar.Date]+30)"} >} Distinct RecordNum)

[Anil_Babu_Samineni]

@asheppardwork To fetch any arbitrary, make sure you have aggregate involved for this field, 

[Trigger_Dt.autoCalendar.Date]

 Because it is important how you are calculating the between data for another associated calendar field. 

[Vegar]

Try this

=Count({<[Return_Dt.autoCalendar.Date]={">=$(=max([Trigger_Dt.autoCalendar.Date])) <=$(=max([Trigger_Dt.autoCalendar.Date])+30)"}>} Distinct RecordNum)

[asheppardwork]

I tried it but I get the same exact counts for 30 and 365 days

Sample Results from your code:

Trigger_dt	Return_DT	30-day_count	365-day_count
05/09/2022	04/02/2024	1	1
09/20/2022	04/02/2024	1	1
11/10/2023	04/01/2024	0	0
04/01/2024	04/01/2024	0	0

What I expect:

Trigger_dt	Return_DT	30-day_count	365-day_count
05/09/2022	04/02/2024	0	0
09/20/2022	04/02/2024	0	0
11/10/2023	04/01/2024	0	0
04/01/2024	04/01/2024	1	0

[asheppardwork]

Do you have an example of what you mean?

[Vegar · Accepted solution]

@asheppardwork 

Sorry about that, I missinterpreted your question.

Set analysis might not be the best way to solve this. You could try 

=-sum((Return_DT - Trigger_dt)<=$(no_of_days))  //where $(no_of_days) is the number of your choice

Another solution could be to calculate the interval in your script while loading the data like in the image below:

Then you could use an easy set expression like 

=count({< dt_interval={"<=$(no_of_days)"}>}dt_interval)

I loaded your dates into  a QV app and using my suggestion it looks like the image below.

[asheppardwork]

Thanks again sir, I am trying your solution but not able to get it to work so let me provide a more complete view of the data

Report_Table:

Emp_ID	Name	Item	Type	Trigger_dt	Return_DT
111222333	Jane Doe	AD76	Upgrade	03/05/2024	03/30/2024
8675309	Robert Ore	ZZ88	Term	04/01/2024	04/01/2024
373737	Jaxon Jeans	XX99	Upgrade	02/01/2023	01/05/2024

Results:

Emp_ID	Name	Item	Type	Trigger_dt	Return_DT	dt_interval	=count({<dt_interval={"<=30"}>}Trigger_dt)
111222333	Jane Doe	AD76	Upgrade	03/05/2024	03/30/2024	25	8
8675309	Robert Ore	ZZ88	Term	04/01/2024	04/01/2024	0	8
373737	Jaxon Jeans	XX99	Upgrade	02/01/2023	01/05/2024	338	8

Desired Results:

Emp_ID	Name	Item	Type	Trigger_dt	Return_DT	dt_interval	30-day Count	365-day Count
111222333	Jane Doe	AD76	Upgrade	03/05/2024	03/30/2024	25	1	0
8675309	Robert Ore	ZZ88	Term	04/01/2024	04/01/2024	0	1	0
373737	Jaxon Jeans	XX99	Upgrade	02/01/2023	01/05/2024	338	0	1

I am not sure why but all my counts are wrong.

In the Load script I'm using:

NUM(Return_Dt - Trigger_dt, '0') as dt_interval

In the sheet I've used each of the following with the various results:

=count({<dt_interval={"<=30"}>}Trigger_dt)

    results in the counts being all 8s?

=count({< dt_interval={"<=$30"}>}dt_interval)

     results in not returning any records

=count({< dt_interval={">= 0 <=31"}>}dt_interval)

      results in the counts being all 8s?

Any Ideas what I'm doing wrong here?

[Vegar]

8 is an odd number to get from that data set. What happens if you select that three and then add the more generic expression

=count(dt_interval)

Do you get 8 as well?

I am asking because I'm suspecting that you have duplicate identical rows in your data set. Eight per employee.

[marcus_sommer]

Make sure that you are looking on the real existing data which isn't practically doable with a chart else only with a table-box including all relevant fields + an unique key-field. If none unique key-field exists you could create one with recno() and/or rowno().

Further if your data-model contained any synthetic keys and/or circular loops resolve them at first ideally by designing the data-model as a star-scheme.

[asheppardwork]

Yes, I did get 8 from just selecting three rows; but each has a unique row id; and those are not being repeated; also not every result is 8, some are 48, some are 16, some are 72, all multiples of 8