Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
asheppardwork
Contributor III
Contributor III

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

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)
Labels (1)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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

Vegar_1-1712816865257.png

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.

Vegar_0-1712816641995.png

 

View solution in original post

11 Replies
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. 

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Vegar
MVP
MVP

Try this

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

asheppardwork
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Do you have an example of what you mean?

Vegar
MVP
MVP

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

Vegar_1-1712816865257.png

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.

Vegar_0-1712816641995.png

 

asheppardwork
Contributor III
Contributor III
Author

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
MVP
MVP

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
Contributor III
Contributor III
Author

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