topic Re: 7 days sliding window classification in QlikView

7 days sliding window classification

aj0031724 — Fri, 14 Aug 2015 10:17:14 GMT

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 ?

Re: 7 days sliding window classification

Gysbert_Wassenaar — Fri, 14 Aug 2015 11:27:54 GMT

See attached qvw.

Re: 7 days sliding window classification

aj0031724 — Fri, 14 Aug 2015 13:33:59 GMT

Hi Gysbert,

Excellent! .Thanks for the quick response and your effort and support.

This really helps me in understanding how to do sliding windows calculation through peek and previous and rangesum.

Re: 7 days sliding window classification

aj0031724 — Wed, 26 Aug 2015 12:13:18 GMT

Hi Gysbert,

I was implementing this into the actual code but facing the issue.

Here in my project ,all the KPI values gets calculated at run time with selection of KPI through user filter as a result I am not able to get the result.

for example qlikview table contains:

KPI NAME KPIFORMULA

a sum(x)+sum(y)

So at run time If I select kpiname->a KPI Value evaluates.

So I do not know in advance the Classification of cell output as per example given.

Peek and Previuos function does not work under Expression of Straight Table at run time.

Can you please help me how can I evaluate the Classification output at run time.

Re: 7 days sliding window classification

aj0031724 — Wed, 26 Aug 2015 13:29:03 GMT

Hi Gysbert,

I was implementing this into the actual code but facing the issue.

Here in my project ,all the KPI values gets calculated at run time with selection of KPI through user filter as a result I am not able to get the result.

for example qlikview table contains:

KPI NAME KPIFORMULA

a sum(x)+sum(y)

So at run time If I select kpiname->a KPI Value evaluates.

So I do not know in advance the Classification of cell output as per example given.

Peek and Previuos function does not work under Expression of Straight Table at run time.

Can you please help me how can I evaluate the Classification output at run time.

Re: 7 days sliding window classification

aj0031724 — Thu, 03 Sep 2015 11:27:11 GMT

Dear Gysbert,

Can I calculate the Classification at run time. Can you please suggest.

Re: 7 days sliding window classification

Gysbert_Wassenaar — Thu, 03 Sep 2015 17:11:18 GMT

Try this expression:

if(ABove(Value)>4 and Value>4,'Alarmed',if(-rangesum((Value>4),(above(Value,1)>4),(above(Value,2)>4),(above(Value,3)>4),(above(Value,4)>4),(above(Value,5)>4),(above(Value,6)>4))>=4,'Highly utilized',if(-rangesum((Value<3),(above(Value,1)<3),(above(Value,2)<3),(above(Value,3)<3),(above(Value,4)<3),(above(Value,5)<3),(above(Value,6)<3))>=6,'Low utilized','Standard')))

Re: 7 days sliding window classification

aj0031724 — Fri, 04 Sep 2015 08:12:08 GMT

Hi Gysbert,

Thanks for the response and workaround.

I tried the above expression in StraightTABLE ...the output is same .

However ,when you select a single date from table the whole classification gets changed to single date.

Like 13/08/2015 ARE älarmed"" BUT ONCE YOU SELECT THE SAME "13/08/2015" FROM STRIGHT TABLE the classification get s changes to "STANDARD".

Also the belwo expression also not working to count HU ,LU .

if(Above(Value)>4 and Value>4,1,0) as Alarmed,
-rangesum((Value>4),(Above(Value,-1)>4),(Above(Value,-2)>4),(Above(Value,-3)>4),(Above(Value,-4)>4),(Above(Value,-5)>4),(Above(Value,-6)>4)) as HU,
-rangesum((Value<3),(Above(Value,-1)<3),(Above(Value,-2)<3),(Above(Value,-3)<3),(Above(Value,-4)<3),(Above(Value,-5)<3),(Above(Value,-6)<3)) as LU

PFA the qvw .

Re: 7 days sliding window classification

Gysbert_Wassenaar — Fri, 04 Sep 2015 08:31:58 GMT

However ,when you select a single date from table the whole classification gets changed to single date.

That's right. That's why this should be set up in the script.

Re: 7 days sliding window classification

aj0031724 — Fri, 11 Sep 2015 12:49:15 GMT

Dear Gysbert,

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

Meaning any expression which will check for Kpi values for date selected and do something like :

: if( values of date selected >4 and values of (date-1) >4 and value of (date-2) >6 then alarmed"s ) simimilarly for others classifications.

Any help/suggestion would be great

Re: 7 days sliding window classification

aj0031724 — Wed, 16 Sep 2015 08:23:10 GMT

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.

Re: 7 days sliding window classification

aj0031724 — Wed, 23 Sep 2015 14:40:11 GMT

Dear Gysbert,

Can you please suggest .

Re: 7 days sliding window classification

aj0031724 — Wed, 28 Oct 2015 11:35:22 GMT

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.

Re: 7 days sliding window classification

Gysbert_Wassenaar — Wed, 28 Oct 2015 12:59:14 GMT

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;

Re: 7 days sliding window classification

aj0031724 — Wed, 28 Oct 2015 14:26:53 GMT

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.

Re: 7 days sliding window classification

aj0031724 — Wed, 28 Oct 2015 14:28:50 GMT

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.

Re: 7 days sliding window classification

Gysbert_Wassenaar — Wed, 28 Oct 2015 14:37:01 GMT

Ah, yes, my mistake. Try changing the line

'Standard')))) as Classification

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

Re: 7 days sliding window classification

aj0031724 — Wed, 28 Oct 2015 14:55:47 GMT

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;

Re: 7 days sliding window classification

Gysbert_Wassenaar — Wed, 28 Oct 2015 15:17:09 GMT

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.

Re: 7 days sliding window classification

aj0031724 — Thu, 29 Oct 2015 07:53:59 GMT

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.