Colouring dates based off the date prior

[nickmarlborough]

I have the following data:

Date	count 1	count 2	count 3
October 1st	10	5	4
September 29th	9	6	4
September 20th	11	2	3

and would like it so the values per column are coloured (red, yellow or green) depending on the value from the date before:

Date	count 1	count 2	count 3
October 1st	10	5	4
September 29th	9	6	4
September 20th	11	2	3

*for the bottom values, the default should be red*

[Kushal_Chawda]

@nickmarlborough  I don't understand the logic yet. What is the rule to colour rows?

[Taoufiq_Zarra]

@nickmarlborough  if I undeerstood correctly you can use :

like =if(sum(count1)>10,green(),red())

[nickmarlborough]

how would you do it if the number of columns extends? so that it is dynamic and isnt specific to the column name?

[nickmarlborough]

also..... its not if over 10....it is if it is over the most recent date then red and if it is below then green and if the same then yellow. 10 was a random number chosen. 

[Qrishna]

if looking for something like above, Try this:

Script: create formatted dates, datenums, and max of date and rowno(you can do this piece on UI too with Aggr and max functions):

data:
load RowNo() as Rowno, *, num(formatted_date)as formatted_date_num;
Load *, Date#(Left(Date, 3) & '/' & purgechar(subfield(Date, ' ', 2), 'st,nd,rd,th') & '/' & 2024, 'MMM/DD/YYYY')as formatted_date;
LOAD Date,
[count 1],
[count 2],
[count 3]
FROM
[https://community.qlik.com/t5/New-to-Qlik-Analytics/Colouring-dates-based-off-the-date-prior/td-p/24...]
(html, codepage is 1252, embedded labels, table is @1);

left join(data)
load max(formatted_date) as max_formatted_date_num,
max(Rowno) as max_rowno
resident data;

on UI:
for cell coloring:
Aggr(if(Rowno=max_rowno,LightRed()
, if(formatted_date_num>max_formatted_date_num
, Red()
, if(formatted_date_num<max_formatted_date_num
, Green()
, RGB(255,165,0))))
, formatted_date)

similarly do for the rest of the expressions as many as you want and yeah you gotta add the measures manually.