Color code the tables based on the Config file

[SK28]

Hi,

I have a requirement,

We should have 3 views that needs to be implemented, in the config file

1. Quarterly view
2. Half Year view
3. No Color Code

Or else only 2 values in Config file

1. Color Code
2. No Color Code

Below is the color logic, for Monthly data

if(Sales > Target, vRed,

if(Sales <Target, vAmber,vGreen))

Requirements:

1. if we select Quarterly view in "CONFIG File", Both Monthly view Till Completed Month's target and Till Completed Quarter the dashboard has to color code
2. if we select Half yearly view in "CONFIG File", Both Monthly view Till Completed Month's target, Till Completed Quarter the dashboard has to color code and completed Half year data has to be color code
3. if No Color code has been selected, in "CONFIG File", all the color code has to be removed even Monthly, Quarterly & Half year

Monthly View
Month	Target	Sales	Colour Code Logic - 2023
JAN	41	41	Green
FEB	41	35	amber
MAR	41	45	red
APR	41		No Colour code
MAY	41		No Colour code
JUN	41		No Colour code
JUL	41		No Colour code
AUG	41		No Colour code
SEP	41		No Colour code
OCT	41		No Colour code
NOV	41		No Colour code
DEC	41		No Colour code
Quarter View
Quarter	Target	Sales	Colour Code Logic - 2023	Notes
Q1	125	150	as 2023 Q1 has completed, it should RED	for 2022, it should be Red
Q2	125	150	as 2023 Q2 has not completed, it should not Colour code	Color logic to be implemented on JULY for Q2
Q3	125	112.5	as 2023 Q3 has not completed, it should not Colour code	Colour Logic to be implemented on OCT for Q3
Q4	125	112.5	as 2023 Q4 has not completed, it should not Colour code	Colour Logic to be implemented on JAN for Q4
Half Year View
Half year	Target	Sales	Colour Code Logic - 2023	Notes
H1	500	600	as 2023 half year has not completed, it should not Colour code	for 2022, it should be Red
H2	500	450	as 2023 half year has not completed, it should not Colour code	for 2022, it should be amber

Based on the Config File, These has to been reflected has anyone help me on this please.

[G3S]

compare the end date of  Q and H  with today() and if today is greater, apply colourcode, if not apply white()

[SK28]

For Quarter End we have Quarterend() function, how to get Half year end ?

[G3S · Accepted solution]

either inline table or an expression -  if(right(Quarter,1)<3,'H1','H2') as HalfYear