Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
lavdata
Contributor
Contributor

4 Week Average with a Window function in Load Script

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. 

Labels (3)
1 Reply
G3S
Creator III
Creator III

a suggestion:
if you removed the text part that says 'week' it could be treated as a number and the window can be derived from that.