Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
Is there any way to detect (script/expression) gradual increase or decrease in a field value over a specified period (Date/Year/Q etc) and then color code based on intensity ?
In the below data i want to color code Rank if , there is gradual increase or decrease in rank over quarter .
1) One way is to look in a given year and detect increment or decrements then color code
2) Other way can be look into any four continuous quarters irrespective of year , detect the increment/decrements and color code
Let say Rank in four quarters of 2018 is
Q4 - 4 , Q3 - 3 , Q2- 2 , Q1-1 ... Then color should be red as there is decrement in RANK and Q4 should be dark red while Q1 should be least .
RANK_TREND:
LOAD * ,
YEAR&'-'&QTR AS YQ
;
LOAD * ,
Year(DOR) AS YEAR ,
'Q' & ceil(month(DOR)/3) as QTR
;
LOAD * ,
Date#(Date,'DD/MM/YYYY') AS DOR ;
LOAD * INLINE [
Client, GROUP, Date, RANK
Tesla, Tesla Electric, 2/2/2018, 1
Tesla, Tesla Electric, 2/5/2018, 2
Tesla, Tesla Electric, 2/7/2018, 3
Tesla, Tesla Electric, 2/11/2018, 4
Tesla, Tesla Electric, 2/2/2019, 4
Tesla, Tesla Electric, 2/5/2019, 3
Tesla, Tesla Electric, 2/7/2019, 2
Tesla, Tesla Electric, 2/11/2019,
Tesla, Tesla Geo, 2/2/2018, 4
Tesla, Tesla Geo, 2/5/2018, 3
Tesla, Tesla Geo, 2/7/2018, 2
Tesla, Tesla Geo, 2/11/2018, 1
Tesla, Tesla Geo, 2/2/2019, 6
Tesla, Tesla Geo, 2/5/2019, 7
Tesla, Tesla Geo, 2/7/2019, 4
Tesla, Tesla Geo, 2/11/2019,
Tesla, Tesla Motors, 2/2/2018, 4
Tesla, Tesla Motors, 2/5/2018, 3
Tesla, Tesla Motors, 2/7/2018, 4
Tesla, Tesla Motors, 2/11/2018, 7
Tesla, Tesla Motors, 2/2/2019, 7
Tesla, Tesla Motors, 2/5/2019, 6
Tesla, Tesla Motors, 2/7/2019, 9
Tesla, Tesla Motors, 2/11/2019,
Tesla, Tesla Solar, 2/2/2018, 2
Tesla, Tesla Solar, 2/5/2018, 4
Tesla, Tesla Solar, 2/7/2018, 9
Tesla, Tesla Solar, 2/11/2018, 9
Tesla, Tesla Solar, 2/2/2019, 8
Tesla, Tesla Solar, 2/5/2019, 9
Tesla, Tesla Solar, 2/7/2019, 4
Tesla, Tesla Solar, 2/11/2019,
];
Thanks & Regards
Shekar
You want to use the LINEST_M function over your data. It will give you the slope of a line when graphing your data.
the farther away from zero the result is the more dramatic the change in your dataset.
@sunny_talwar ..
Hi Sunny Bhai ,
Can above be coded or any set expression , just to avoid LIN_M ?
I want to use similar concept on IPL data to find list of those 5 continuous overs where Maximum runs have been scored .