Dear Team,
PFB the requirement with example:
One Table Structure Of KPI:
KPINAME KPIFORMULA
A SUM(10) + SUM(20)
B SUM(30)+ SUM(40)
Note: IN our dashboard KPI value gets evelauted at run time through use of filter selction of KPI WITH VARIABLE AS BELOW EXAMPLE:
vkpievaluation=MaxString({$<KPINAME={'$(vSelectedKpi1)'}>} KPIFORMULA)
and we use $(vkpievaluation) in an expression to get the value of KPI.
IF USER SEELCTS kpiname->A KPIVALUE-->$(vkpievaluation)=30.
AnOTHER TABLE STRUCTURE OF CELL WITH DATE AS EXAMPLE:
DATE CELL
01/08/2015 C1
05/08/2015 C1
10/08/2015 C1
15/08/2015 C1
20/8/2015 C1
25/08/2015 C1
31/08/2015 C1
01/08/2015 C2
05/08/2015 C2
10/08/2015 C2
15/08/2015 C2
20/8/2015 C2
25/08/2015 C2
31/08/2015 C2.
Requirement:
MARK EACH DATE WITH BELOW CLASSIFICATION BY DOING 7 DAYS SLIDING WINDOW.
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 must 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
CASSIFICATION LOGIC:
Alarmed --> last 2 days above 4(calculated KPI value)
High --> more than 3 occurences above 4 (4 or more occurences above 4)
Standard---> (3 or less occurences above 4) AND (5 or less occurences below 3)
Low Utilized ---> more than 5 occurences below 3 (6 or more occurences below 3)
Suppose User Filter is as below:
a)KPI NAME-> A
B) Date-> 31/08/2015
SO dashboard output requirement:
Per KPI chosen from the KPI list (user may choose multiple KPIs) and the cells inside the current filter show the following:
a. Pie Chart of percentage breakdown to Alarmed Cells, Highly Utilized Cells, Standard Cells and Low Utilized cells
(Alarmed, High and Low - if there is a KPI from the filtered KPI list which is Alarmed High or Low).
b) Straight TABLE OUTPUT WITH BELOW COLUMNS:
DATE KPI CELL VALUE CLASSIFICCATIONS
31/08/2015 A C1 $(vkpievaluation) ALARMED (IF THE EVALATED KPIVALUE FALLS IN THIS RANGE)
31/08/2015 A C2 $(vkpievaluation) HIGH ((IF THE EVALATED KPIVALUE FALLS IN THIS RANGE).
I was using the below script but since I don't know in advance the KPI value for sector/cell ,I need some workaround to get the Classification at run time.
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'))) as Classification,
Please advice/suggest .
