Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Setting a Flag in Script is an easy approach. Please share a sample data.
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;
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?
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!
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;
Dimension: Prenom, Date_Selle
Expression: Str
Background Color: If(Flag='Y' And Str='X', LightGray())
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!
Try like this.
If(-Sum(Str='X')>=3, 'Y', 'N') as Flag
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.