I am currently in need of assistance with Range Analysis techniques which can be utilized in my script to create a QVD. My issue is with implementing the proper Range Analysis technique to correctly identify a consecutive series of hours that exceed unique criteria.
For an entity in my data set, hourly data is evaluated and can only contain one threshold label.
To obtain a threshold label of Extreme, the hourly sales total could be the first, second, or third hour in a series of hours to exceed both 15% of target and $20,000. There must be at least three consecutive hours all exceeding the threshold to obtain this label. Two consecutive hours would receive no label.
Currently, I am only able to identify the second and third hour in a series correctly when performing the initial load of data. Any assistance with identifying the range analysis technique I should utilize will be greatly appreciated.
The ultimate goal is to create a QVD containing the properly applied labels to the hours which exceeded the thresholds. This is needed due to the labels will affect calculations in subsequent analysis.
- Twenty distinct entities are being evaluated under the same conditions and calculations
- Each entity has a different hourly target amount
- Data for each entity is hourly
- Deviations from sales targets can be positive or negative
- a. Negative Deviation - hourly sales are less than the hourly sales target
- b. Positive Deviation - hourly sales are greater than the hourly sales target
- If a sales target deviation is reoccurring in the same direction, it needs to be identified and the trend labeled/ grouped into one of the following thresholds
- a. Extreme - both 15 % of the hourly target amount and $20,000 in each hour for 3 consecutive hours or more in the same direction
- b. High - both 7.5 % of the hourly target amount and $10,000 in each hour for 6 consecutive hours or more in the same direction
- c. Moderate - both 1.5 % of the hourly target amount and $5,000 in each hour for 12 consecutive hours or more in the same direction
- d. Low - both 1.5 % of the hourly target amount and $2,000 in each hour for 24 consecutive hours or more in the same direction