Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

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 

2 Replies
Highlighted
Partner
Partner

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

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/Statistic...

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

Highlighted
Specialist
Specialist

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

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