Is there any way to detect gradual increase or decrease in a field value and then color code ?

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Is there any way to detect gradual increase or decrease in a field value and then color code ?

chriscammers — Mon, 09 Sep 2019 14:43:34 GMT

You want to use the LINEST_M function over your data. It will give you the slope of a line when graphing your data.

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/StatisticalAggregationFunctionsinCharts/linest_m.htm

the farther away from zero the result is the more dramatic the change in your dataset.

Re: Is there any way to detect gradual increase or decrease in a field value and then color code ?

shekhar_analyti — Mon, 09 Sep 2019 14:55:49 GMT

@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 .

topic Is there any way to detect gradual increase or decrease in a field value and then color code ? in QlikView

Is there any way to detect gradual increase or decrease in a field value and then color code ?

Re: Is there any way to detect gradual increase or decrease in a field value and then color code ?

Re: Is there any way to detect gradual increase or decrease in a field value and then color code ?