2 Replies Latest reply: Jul 23, 2014 3:07 PM by Justin Pikulski RSS

    Range Analysis Techinques - Assistance Needed

      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