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]

Setting a Flag in Script is an easy approach. Please share a sample data. 

[alicecollot]

Thanks for your answer! Here is a sample data and the script I use :

LOAD distinct
A as Nom,
B as Prenom,
Date(C) as Date_Selle,
Max(Num(E)) as Selle
FROM
[$(VCheminIn)\Test_Selles.xlsx]
(ooxml, no labels, table is Feuil1)
Group by A, B, C;

[Saravanan_Desingh]

So, you want to count the missing only between the dates 6/8/20 to 6/11/20?

In that case you will get Irène and Jean. Am I right?

[alicecollot]

Later, I will receive data from other dates. But in this case, yes. I want to color the 3 missing values of Irène and Jean. 

Thank you!

[Saravanan_Desingh]

Check 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 Feuil1);


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(-Sum(Str='X')=3,'Y','N') As Flag, -Sum(Str='X') As Cnt
Resident tab1
Group By Prenom
;

Drop Table tab2;

[Saravanan_Desingh]

Dimension: Prenom, Date_Selle

Expression: Str

Background Color:  If(Flag='Y' And Str='X', LightGray())

[alicecollot]

Thanks for help. 

I have tried it and added an other date. So, I still have a little problem : I want to color the cases when there are 3 or more 'X' in a row and not 3 'X' in total. I don't know how to change this "If(-Sum(Str='X')=3, 'Y', 'N') as Flag" to make it works.

Have a nice a day!

[Saravanan_Desingh]

Try like this.

If(-Sum(Str='X')>=3, 'Y', 'N') as Flag

[alicecollot]

I've already done that for the other dates but here a picture of my problem :

For Jean and Christian, I don't want to color the cases because there is no 3 'X' in a row. This is not the same data set because I work with confidential data but I can change the set I sent to have this problem.