How to define background color if there are more than 3 missing values in pivot table ?

[alicecollot]

Hi everyone,

I have a question about missing values in pivot table. 

I have created this pivot table to follow different biometrics each day :

I have colored each missing values with yellow but it's not what I wanted to do.

I have to color the missing values if they are missing for 3 days or more.

My question is if it's possible to do that with a function in Background Color ? Or as they are missing values, I will have to work in the script (by turning them into numeric values or an other trick) ?

Thanks for your help, 

Alice.

[Saravanan_Desingh]

My code wont check the continuous 3 X's. Let me change the code and let you know.

[Saravanan_Desingh · Accepted solution]

Can you try this?

tab1:
LOAD RowNo() As RowID, A As Nom, 
     B As Prenom, 
     Date(Date#(C,'YYYY-MM-DD')) As Date_Selle, 
     D, 
     E
FROM
[C:\Users\sarav\Downloads\Test_Selles.xlsx]
(ooxml, no labels, table is Sheet1);


tab2:
LOAD Distinct Prenom
Resident tab1;
Left Join(tab2)
LOAD Date(Min_Selle+IterNo()-1) As Date_Selle
While (Min_Selle+IterNo()-1) <= Max_Selle
;
LOAD Date(Max(Date_Selle)) As Max_Selle,
	 Date(Min(Date_Selle)) As Min_Selle
Resident tab1;	 

Right Join(tab1)
LOAD * Resident tab2;

Left Join(tab1)
LOAD Prenom, Date_Selle, If(Concat(DISTINCT E)='','X',Concat(DISTINCT E,',')) As Str
Resident tab1
Group By Prenom, Date_Selle
;

Left Join(tab1)
LOAD Prenom, If(Index(Concat(Str='X','',Date_Selle),'-1-1-1'),'Y','N') As Flag
Resident tab1
Group By Prenom
;

Drop Table tab2;

[Saravanan_Desingh]

Check Christiane here. She has three X'x. But not continuously. So, its not been colored.

[alicecollot]

Thanks a lot! It works perfectly!

Alice.

[Saravanan_Desingh]

You welcome. Can you please mark the solution if answered?