7 days sliding window classification

[aj0031724]

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 ?

[Gysbert_Wassenaar]

See attached qvw.

[aj0031724]

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.

[aj0031724]

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.

[aj0031724]

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.

[aj0031724]

Dear Gysbert,

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

[Gysbert_Wassenaar]

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

[aj0031724]

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 .

[Gysbert_Wassenaar]

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.

[aj0031724]

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