Re: 7 days sliding window classification - Page 2 - Qlik Community

aj0031724 · ‎2015-08-14

Team,

Require you help to know how to calculate 7 day sliding window date range .

The 7 day logic means a cell is classified according to if in the last 7 days the classification logic comes out TRUE.

So for each day you look at the 6 days previous to the day + the day considered (total of 7 days) and then do the logic and decide which classification is TRUE for that day.

Classification is of 4 types:

a)ALARMED

B)HIGH

C)STANDARD

D)LOW

I:E I nned to mark each date with this 4 classification based on above logic.

sAMPLE EXCEL ATTACHED WITH CLASSIFICATION LOGIC.

can you please advice how to do this ?

aj0031724 · ‎2015-09-16

Dear Gysbert,

Can you please suggest the way around apart from Above.

is there any ways of using set analysis for this to overcome the use of Above function as an expression.

can we keep one straight table just to show output as static and another clone of that with fetching "CLASSIFICATION"" from static output from different straight table.

Please help.

aj0031724 · ‎2015-09-23

Dear Gysbert,

Can you please suggest .

aj0031724 · ‎2015-10-28

Dear Gysbert,

I have implemented this in load script the solution suggested with peek and previous ..but this classifcationb comes out true when you just have dates sorted in ascending order forone particular combination.

I need to use GROUP BY KPI,RNC,UTRANCELL,SITE,DATE inside the classification logic so that each unique combination of data ets correct classifications. for example:

We need to group data based on KPI,RNC,UTRANCELL,DATE,site from master table and apply same LOGIC AS BELOW FOR OUTPUT:

I am unable to do SO .Can you please suggest /advice how to implement this in load script.

I nned the classification to be grouped based on KPI,RNC,UTRANCELL,DATE,site as highlighted in color sets.

data sets example:

_KPI_NAME_GenericKpisActivatedByUser	UTRANCELL	RNC	DATE	SITE	RESULT	CLASSIFICATION
COMB CS DROPS	ASESP22	DPP1R01	06.12.14	ASESP	3
COMB CS DROPS	ASESP22	DPP1R01	06.13.14	ASESP	4
COMB CS DROPS	ASESP22	DPP1R01	06.14.14	ASESP	11
COMB CS DROPS	ASESP22	DPP1R01	06.15.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.16.14	ASESP	7
COMB CS DROPS	ASESP22	DPP1R01	06.17.14	ASESP	5
COMB CS DROPS	ASESP22	DPP1R01	06.18.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.19.14	ASESP	8
COMB CS DROPS	ASESP22	DPP1R01	06.20.14	ASESP	7
COMB CS DROPS	ASESP22	DPP1R01	06.21.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.22.14	ASESP	2
COMB CS DROPS	ASESP22	DPP1R01	06.23.14	ASESP	10
COMB CS DROPS	ASESP22	DPP1R01	06.24.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.25.14	ASESP	19
COMB CS DROPS	ASESP22	DPP1R01	06.26.14	ASESP	2
COMB CS DROPS	ASESP22	DPP1R01	06.27.14	ASESP	4
COMB CS DROPS	ASESP22	DPP1R01	06.28.14	ASESP	1
COMB CS DROPS	ASESP22	DPP1R01	06.29.14	ASESP	2
COMB CS DROPS	ASESP22	DPP1R01	06.30.14	ASESP	22
COMB CS DROPS	ASESP22	DPP1R01	07.01.14	ASESP	3
COMB CS DROPS	ATACM22	DPP1R01	06.12.14	ATACM	9
COMB CS DROPS	ATACM22	DPP1R01	06.13.14	ATACM	9
COMB CS DROPS	ATACM22	DPP1R01	06.14.14	ATACM	11
COMB CS DROPS	ATACM22	DPP1R01	06.15.14	ATACM	5
COMB CS DROPS	ATACM22	DPP1R01	06.16.14	ATACM	6
COMB CS DROPS	ATACM22	DPP1R01	06.17.14	ATACM	12
COMB CS DROPS	ATACM22	DPP1R01	06.18.14	ATACM	5
COMB CS DROPS	ATACM22	DPP1R01	06.19.14	ATACM	7
COMB CS DROPS	ATACM22	DPP1R01	06.20.14	ATACM	5
COMB CS DROPS	ATACM22	DPP1R01	06.21.14	ATACM	1
COMB CS DROPS	ATACM22	DPP1R01	06.22.14	ATACM	4
COMB CS DROPS	ATACM22	DPP1R01	06.23.14	ATACM	2
COMB CS DROPS	ATACM22	DPP1R01	06.24.14	ATACM	5
COMB CS DROPS	ATACM22	DPP1R01	06.25.14	ATACM	7
COMB CS DROPS	ATACM22	DPP1R01	06.26.14	ATACM	15
COMB CS DROPS	ATACM22	DPP1R01	06.27.14	ATACM	9
COMB CS DROPS	ATACM22	DPP1R01	06.28.14	ATACM	6
COMB CS DROPS	ATACM22	DPP1R01	06.29.14	ATACM	4
COMB CS DROPS	ATACM22	DPP1R01	06.30.14	ATACM	6
COMB CS DROPS	ATACM22	DPP1R01	07.01.14	ATACM	9

I tried to loop through unqiue combination and GROUP BY requires some aggregation.

Please help.

Gysbert_Wassenaar · ‎2015-10-28

I think you can do this in two steps:

1. Load the data in a temporary table and create a new key field from the fields that should be considered a group.

2. Load the data from the temporary table and sort it by the new key field and the date field and calculate the classification.

// Step 1

Temp:

LOAD *, autonumber( KPI &'|'&RNC&'|'&UTRANCELL) as Key

From ...source...;

// Step 2

Result:

LOAD *,

if(Previous(Key)=Key,if(Previous(Value)>4 and Value>4,'Alarmed',

if(-rangesum((Value>4),(peek(Value,-1)>4),(peek(Value,-2)>4),(peek(Value,-3)>4),(peek(Value,-4)>4),(peek(Value,-5)>4),(peek(Value,-6)>4))>=4,'Highly utilized',

if(-rangesum((Value<3),(peek(Value,-1)<3),(peek(Value,-2)<3),(peek(Value,-3)<3),(peek(Value,-4)<3),(peek(Value,-5)<3),(peek(Value,-6)<3))>=6,'Low utilized',

'Standard'))),'Standard') as Classification

Resident Temp;

Drop Table Temp;

talk is cheap, supply exceeds demand

aj0031724 · ‎2015-10-28

Dear Gysbert,

I followed the above suggested logic in 2 steps.

But It skips the classification beginning each starding date for unique combination.

Please see if I am doing something wrong as per your suggestion.

aj0031724 · ‎2015-10-28

Avinash Jain 28 Oct, 2015 10:26 AM (in response to Gysbert Wassenaar )

Dear Gysbert,

I followed the above suggested logic in 2 steps.

But It skips the classification beginning each starding date for unique combination.

Please see if I am doing something wrong as per your suggestion.

Gysbert_Wassenaar · ‎2015-10-28

Ah, yes, my mistake. Try changing the line

'Standard')))) as Classification

to

'Standard'))),'Standard') as Classification

talk is cheap, supply exceeds demand

aj0031724 · ‎2015-10-28

Dear Gysbert,

Yes with this "classification "" is coming for all dates.

But to count HU,LU respectively it is getting cumulative.

should I use the same condition for HU AND LU fields.

Value:

LOAD *,

if(Previous(Key)=Key,if(Previous(Value)>4 and Value>4,'Alarmed',

if(-rangesum((Value>4),(peek(Value,-1)>4),(peek(Value,-2)>4),(peek(Value,-3)>4),(peek(Value,-4)>4),(peek(Value,-5)>4),(peek(Value,-6)>4))>=4,'Highly utilized',

if(-rangesum((Value<3),(peek(Value,-1)<3),(peek(Value,-2)<3),(peek(Value,-3)<3),(peek(Value,-4)<3),(peek(Value,-5)<3),(peek(Value,-6)<3))>=6,'Low utilized',

'Standard'))),'STANDARD') as Classification,

if(Previous(Key)=Key,-rangesum((Value>4),(peek(Value,-1)>4),(peek(Value,-2)>4),(peek(Value,-3)>4),(peek(Value,-4)>4),(peek(Value,-5)>4),(peek(Value,-6)>4)))AS HU,
if(Previous(Key)=Key,
-rangesum((Value<3),(peek(Value,-1)<3),(peek(Value,-2)<3),(peek(Value,-3)<3),(peek(Value,-4)<3),(peek(Value,-5)<3),(peek(Value,-6)<3)))AS LU

Resident Temp
ORDER BY Key,Date;

Gysbert_Wassenaar · ‎2015-10-28

Yes, but you need an other change and that should be applied to the classification calculation too. The lines I posted earlier won't give a correct result for all records. You'll have to modify the peeks so that the key check is done there too:

peek(Value,-1) should become if(peek(Key,-1)=Key,peek(Value,-1),0)

peek(Value,-3) should become if(peek(Key,-2)=Key,peek(Value,-2),0)

etc...

Otherwise the classification of the first three records of a key will use values from a previous key.

talk is cheap, supply exceeds demand

aj0031724 · ‎2015-10-29

Dear Gysbert,

I modified the classification script as below :

Value:

LOAD *,

if(Previous(Key)=Key,if(Previous(Value)>4 and Value>4,'Alarmed',

if(-rangesum((Value>4),(if(peek(Key,-1)=Key,peek(Value,-1),0)>4),(if(peek(Key,-2)=Key,peek(Value,-2),0)>4),(if(peek(Key,-3)=Key,peek(Value,-3),0)>4),(if(peek(Key,-4)=Key,peek(Value,-4),0)>4),(if(peek(Key,-5)=Key,peek(Value,-5),0)>4),(if(peek(Key,-6)=Key,peek(Value,-6),0)>4))>=4,'Highly utilized',

if(-rangesum((Value<3),(if(peek(Key,-1)=Key,peek(Value,-1),0)<3),(if(peek(Key,-2)=Key,peek(Value,-2),0)<3),(if(peek(Key,-3)=Key,peek(Value,-3),0)<3),(if(peek(Key,-4)=Key,peek(Value,-4),0)<3),(if(peek(Key,-5)=Key,peek(Value,-5),0)<3),(if(peek(Key,-6)=Key,peek(Value,-6),0)<3))>=6,'Low utilized',

'Standard'))),'STANDARD') as Classification,

if(Previous(Key)=Key,-rangesum((Value>4),(if(peek(Key,-1)=Key,peek(Value,-1),0)>4),(if(peek(Key,-2)=Key,peek(Value,-2),0)>4),(if(peek(Key,-3)=Key,peek(Value,-3),0)>4),(if(peek(Key,-4)=Key,peek(Value,-4),0)>4),(if(peek(Key,-5)=Key,peek(Value,-5),0)>4),(if(peek(Key,-6)=Key,peek(Value,-6),0)>4)))AS HU,
if(Previous(Key)=Key,
-rangesum((Value<3),(if(peek(Key,-1)=Key,peek(Value,-1),0)<3),(if(peek(Key,-2)=Key,peek(Value,-2),0)<3),(if(peek(Key,-3)=Key,peek(Value,-3),0)<3),(if(peek(Key,-4)=Key,peek(Value,-4),0)<3),(if(peek(Key,-5)=Key,peek(Value,-5),0)<3),(if(peek(Key,-6)=Key,peek(Value,-6),0)<3)))AS LU

Resident Temp
ORDER BY Key,Date;

With this the classification changed a lot .Previuous 08-01-2015 and "09-01-2015"" were standard ,now changed to " LOW UTLIZED".

Requesting you to Please check and confirm if the script is correct as suggested.