
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Complex Date Creation from a Resident Table to a new chartable table
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.
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
- 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
- I need the Total Number of Returns during the same time as item 1.
- 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)
- I need to be able to show these numbers as
- A whole by Month/Year of the Rate Calc Date
- Grouped by Department
- Grouped by Department and Trigger Date
Finally it needs to be able to be charted in a bar chart
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
