Range Analysis Techinques - Assistance Needed

[#1]

QlikCommunity,

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.

Background:

1. Twenty distinct entities are being evaluated under the same conditions and calculations
2. Each entity has a different hourly target amount
3. Data for each entity is hourly
4. Deviations from sales targets can be positive or negative
   1. a. Negative Deviation - hourly sales are less than the hourly sales target
   2. b. Positive Deviation - hourly sales are greater than the hourly sales target
5. 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
   1. 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
   2. 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
   3. 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
   4. 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

[Gysbert_Wassenaar · Accepted solution]

You can do this using the peek function, but you'll need a big if statements, especially for the 12 and 24 hour series. The data needs to be ordered by Entity, Date and then Hour. For the three hour series the if statement (for the positive deviation) would look something like this:

if( Entity=peek(Entity,-1)

    and Entity=peek(Entity,-2)

    and HourlySales > HourlyTarget*0.15 and HourlySales > 20000

    and peek(HourlySales,-1) > HourlyTarget*0.15 and peek(HourlySales,-1) > 20000

    and peek(HourlySales,-2) > HourlyTarget*0.15 and peek(HourlySales,-2) > 20000

   , 1, 0) as ThresholdA

[#3]

A variation of this is what I utilized to resolve my issue.  Thank you again for your assistance!