Complex Date Creation from a Resident Table to a new chartable table

[asheppardwork]

I need to find the rate of items returned during a given 30-day window from before any given calendar date. 

Any help is most appreciated as I cannot seem to make it work.

I have tried this with a do while loop in my load statement but it bloated from the original sample of 82,000 records to over 2.8 million.  This is just a test set and the real set will be 100s of thousands of records each month.

I have three dates involved:

a) The Trigger Date - date of a triggering event - every record has one
b) The Return Date - date of the return after the triggering event - not every record/item has one
c) The Rate Calc Date - any given calendar date in the last 3 years

1. I need the Total Number of Events - this is number of records that have trigger dates that come 30 days before the Rate Calc Date
2. I need the Total Number of Returns during the same time as item 1.
3. I need to take the Total Number of Returns and divide by the Total Number of Events to get the Return Rate for each Rate Calc Date (aka every calendar date during the last 3 years)
4. I need to be able to show these numbers as

1. A whole by Month/Year of the Rate Calc Date
2. Grouped by Department
3. Grouped by Department and Trigger Date

Finally it needs to be able to be charted in a bar chart

Data Sample:

RecNo,Trigger_Date,Return_Date,Item,EEName,Dept
101,4/15/2021,5/10/2021,454547,John Doe,Legal
102,4/15/2021,5/6/2021,454797,Jane Dough,IT
103,4/15/2021,6/7/2021,455047,Doug Funny,Sales
104,10/4/2021,,455297,Han Solo,Security
129,10/19/2021,11/18/2021,461547,Jimmy Klark,Legal
105,11/22/2021,,455547,Din Dejarin,Legal
106,12/7/2021,1/15/2022,455797,Greedo,IT
107,2/1/2022,2/23/2022,456047,Boba Fett,Sales
108,2/1/2022,9/6/2023,456297,Jabba Hutt,Security
109,2/1/2022,8/16/2022,456547,Sea Threepio,Legal
110,4/21/2022,9/14/2022,456797,Arty Detoo,IT
111,7/13/2022,,457047,Rea Palpatine,Sales
112,7/19/2022,8/10/2022,457297,Ben Solo,Security
113,8/18/2022,8/30/2022,457547,Bruce Wayne,Legal
114,10/19/2022,11/2/2022,457797,Lance Calrusian,IT
115,10/28/2022,11/14/2022,458047,Ralph Tuttle,Sales
116,1/6/2023,1/19/2023,458297,Leo Tuttle,Security
117,1/6/2023,2/7/2023,458547,Mike Tuttle,Legal
118,1/19/2023,,458797,Donny Tuttle,IT
119,2/4/2023,,459047,Zoe Spengler,Sales
120,2/4/2023,,459297,Winston Zeddemore,Security
121,3/20/2023,,459547,Ray Stanz,Legal
122,3/20/2023,4/11/2023,459797,Egon Spengler,IT
123,8/16/2023,,460047,Peter Venkman,Sales
124,8/16/2023,,460297,Lucas Sky,Security
125,8/16/2023,9/9/2023,460547,Leia Sky,Legal
126,9/12/2023,,460797,Anton Stark,IT
127,9/19/2023,10/27/2023,461047,Chewy Bakka,Sales
128,9/19/2023,,461297,YoYo Dah,Security

Attached bar-chart sample. 

[asheppardwork]

It should come out looking like this:

Dept, Trigger_Date, Rate_Date, 30Day_Rate, 30Day_Returns, 30Day_Totals
Sales, 2023-01-01, 2023-01-01, 58.5%, 1696, 2901
Legal, 2023-01-01, 2023-01-01, 57.5%, 1609, 2800
AP, 2023-01-01, 2023-01-02, 58.4%, 1630, 2790
IT, 2023-01-01, 2023-01-02, 59.4%, 1737, 2926

[marcus_sommer]

I must admit that I'm not sure that I understand your aim but I believe that neither your script-aggregation in loops nor the shown output will be really expedient because the dates seems to have a from-to relation which couldn't be simply connected to month and year within the UI and the results should be more or less flexible in regard to selections (which would require to calculate nearly all possible combinations in the script).

I could imagine that you rather need a date-resolution like by an IntervalMatch - Qlik Community - 1464547 (which could be also approached with an internal load while-loop). Parallel to it the differences between the various dates might be calculated and/or also offsets to previous/following  sales and similar stuff.

Also connecting the date with a master-calendar which includes not only year and month else also running counter for dates and maybe weeks/month/years ... which might be in the end sufficient to define the date-scope just per set analysis