Range Analysis Techinques - Assistance Needed

Sat, 30 Nov 2013 21:46:25 GMT

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:

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
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
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

Re: Range Analysis Techinques - Assistance Needed

Gysbert_Wassenaar — Sun, 01 Dec 2013 12:26:44 GMT

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

Re: Range Analysis Techinques - Assistance Needed

Wed, 23 Jul 2014 19:07:44 GMT

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

topic Range Analysis Techinques - Assistance Needed in QlikView

Range Analysis Techinques - Assistance Needed

Re: Range Analysis Techinques - Assistance Needed

Re: Range Analysis Techinques - Assistance Needed