Hello All,
I am trying to compute a rolling 4 week average for sales units to use in a calculation of inventory levels. I want to do this in the load script to assign the values to the correct store ID and part ID combinations. When sorting the data, I do not have actual date values to sort against. They are formatted as ex. 2024WEEK 01. Qlik tables sort this correctly as text, but the script doesn't seem to either sort or partition the data properly.
I have created a unique ID value combining the part ID and store ID to look like partAstore3 to avoid multiple partitions. Here is my current window function.
Window(Avg(SalesUnits), uniqueID, 'ASC', weekID, SalesUnits >= 0, -3, 0) as Rolling4wkAvg
This is then used to determine inventory levels by dividing current inventory by the 4 week average.
currentInventory / Rolling4wkAvg as InvLevel
I cannot seem to figure out how it is coming up with the values it is, but they are not correct. Any help is greatly appreciated.
