topic Re: How can you use set analysis to count the number of items with a date that is between two other dates in App Development

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

asheppardwork — Tue, 09 Apr 2024 16:19:47 GMT

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)

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

Anil_Babu_Samineni — Wed, 10 Apr 2024 04:54:13 GMT

@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.

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

Vegar — Wed, 10 Apr 2024 04:57:00 GMT

Try this

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

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

asheppardwork — Wed, 10 Apr 2024 15:52:53 GMT

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

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

asheppardwork — Wed, 10 Apr 2024 15:53:26 GMT

Do you have an example of what you mean?

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

Vegar — Thu, 11 Apr 2024 06:29:49 GMT

@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.

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

asheppardwork — Thu, 11 Apr 2024 19:58:11 GMT

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?

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

Vegar — Fri, 12 Apr 2024 05:10:15 GMT

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.

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

marcus_sommer — Fri, 12 Apr 2024 05:24:59 GMT

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.

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

asheppardwork — Fri, 12 Apr 2024 15:19:10 GMT

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

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

marcus_sommer — Fri, 12 Apr 2024 15:50:00 GMT

Your row id might be unique within the source and maybe also in the origin Qlik load. But if there are any join or loop approaches in your script the records might be duplicated. By joining approaches may even recno() and rowno() not unique in regard to the existing records.

Therefore add at the end of your joins and/or the script something like this:

qualify *; x: load *, rowno() as RowNo resident YourTable; unquality *;

and then using the fields of this island-table within a table-box. I suspect you will then see a lot of duplication ...

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

asheppardwork — Wed, 17 Apr 2024 20:38:29 GMT

Thank you sir, I have decided to try another approach, but I think your solution would have worked if my data were cleaner. Again, I appreciate the help and apologies for taking so long to approve; been re-thinking my entire career, lol.