Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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