Hi everyone,
I hope you're all doing well ! I have a question and would really appreciate your help.
I've been working on a function to create a NOK tracking chart that shows the NOK percentages over a specific date range. Here's a quick rundown of what i want my function to do :
- I get the start and end dates for the current month.
- Next i creat new table to store the NOK percentages for each date and It goes through each day from the start of the month to today.
- Then it inserts the date and the corresponding NOK percentages into the new table.
- Finally, it creates a line graph showing the NOK percentages over the date range.
I've got the function mostly working, but I'm running into a bit of trouble.
TOP5:get_dates = Function({},
today_date = Format(Today(), "yyyy-mm-dd");
start_of_month = Format(Date Increment(Today(), "Month", 0, "Start"), "yyyy-mm-dd");
evallist({start_of_month, today_date});
);
TOP5:calculate_nok_percentages = Function({dt},
{start_of_month, today_date} = TOP5:get_dates();
dt_top5 = dt << Summary(
Group(:Date, :Nom de fichier, :Ref Produit, :ProductName, :Baie, :ICT_FCT, :Result, :Reason Error, :CodeError),
Freq("Aucun(e)"),
Weight("Aucun(e)"),
invisible
);
nameFilesNok = Associative Array(Column(dt_top5, "Nom de fichier") << Get Values) << Get Keys;
total_nb_total_ict = 0;
total_nb_total_fct = 0;
total_nb_nok_ict = 0;
total_nb_nok_fct = 0;
For(k = 1, k <= N Items(nameFilesNok), k++,
FileRows = dt_top5 << get rows where(
:File Name == nameFilesNok[k] & Num(Char(:Date)) >= Num(start_of_month) & Num(Char(:Date)) <= Num(today_date) & Contains(:File Name, "GOLDEN") == 0
);
nb_total_ict = 0;
nb_total_fct = 0;
nb_nok_ict = 0;
nb_nok_fct = 0;
total_rows = N Items(FileRows);
For(j = 1, j <= total_rows, j++,
If(dt_top5:ICT_FCT[FileRows[j]] == "ICT",
nb_total_ict = nb_total_ict + dt_top5:Nb. de lignes[FileRows[j]];
If(dt_top5:Result[FileRows[j]] == "NOK",
nb_nok_ict = nb_nok_ict + dt_top5:Nb. de lignes[FileRows[j]]
);
, dt_top5:ICT_FCT[FileRows[j]] == "FCT",
nb_total_fct = nb_total_fct + dt_top5:Nb. de lignes[FileRows[j]];
If(dt_top5:Result[FileRows[j]] == "NOK",
nb_nok_fct = nb_nok_fct + dt_top5:Nb. de lignes[FileRows[j]]
);
)
);
total_nb_total_ict += nb_total_ict;
total_nb_total_fct += nb_total_fct;
total_nb_nok_ict += nb_nok_ict;
total_nb_nok_fct += nb_nok_fct;
);
percent_nok_ict = (total_nb_nok_ict / total_nb_total_ict) * 100;
percent_nok_fct = (total_nb_nok_fct / total_nb_total_fct) * 100;
Eval List({percent_nok_ict, percent_nok_fct});
);
TOP5:create_nok_tracking_chart = Function({dt},
{start_of_month, today_date} = TOP5:get_dates();
dt_nok = New Table("NOK Percentages",
Add Rows(0),
New Column("Date", Character),
New Column("Percent NOK ICT", Numeric),
New Column("Percent NOK FCT", Numeric)
);
For(i = Num(start_of_month), i <= Num(today_date), i++,
// Correct date formatting
today_str = Format(Date(i), "yyyy-mm-dd");
Eval List({start_of_month, today_date}) = Eval List({today_str, today_str});
result = TOP5:calculate_nok_percentages(dt);
Insert Into(dt_nok,
today_str,
result[1],
result[2]
);
);
Graph Builder(
Size( 700, 500 ),
Show Control Panel( 0 ),
Variables( X( :Date ), Y( :Percent NOK ICT ), Y( :Percent NOK FCT ) ),
Elements(
Line(
X,
Y( 1 ),
Legend( 6 ),
Line Color( "Red" )
),
Line(
X,
Y( 2 ),
Legend( 8 ),
Line Color( "Blue" )
)
),
Legend Position( "Top" )
);
);
i did get this error message :
/*:
Unresolved name: Date JSL when accessing or evaluating "Date JSL". Date JSL( i ) /*###*/
Could anyone provide some guidance or tips on how to improve this ?
Thanks a lot!
You are most likely getting that error as your date variable is a string and Format() requires you to have a numeric variable. My guess that error is from this
date is same as today_date, today_date comes from TOP5:get_dates() which returns strings. Keep the dates as numeric values and you will avoid all of these issues (there is no need to use Num(Char()) conversions, especially when you are starting from Today() which is a numeric value).
I suggest you read Date-Time parts from Scripting Guide, starting from this https://www.jmp.com/support/help/en/18.0/#page/jmp/datetime-functions-and-formats.shtml#
I took your suggestions into account and tried this method. However, I'm still facing an issue: my function returns a table with points in rows.
and I kept date = today_date; because when I run the script with today_date as a parameter directly, JMP doesn't respond. I don't understand why assigning date = today_date handles the run more efficiently.
TOP5:get_dates_percent = Function({},
today_date = Today();
start_of_month = Date Increment(Today(), "Month", 0, "Start");
evallist({start_of_month, today_date});
);
TOP5:calculate_nok_percentages_over_time = Function({dt},
{start_of_month, today_date} = TOP5:get_dates_percent();
dt_results = New Table("NOK Percentages Over Time",
New Column("Date", Numeric),
New Column("Percent_NOK_ICT", Numeric),
New Column("Percent_NOK_FCT", Numeric)
);
// Set the format for the Date column
Column(dt_results, "Date") << Data Type(
Numeric,
Format("Format Pattern", "<YYYY>-<MM>-<DD>", 10),
Input Format("Format Pattern", "<YYYY>-<MM>-<DD>"),
Format("Date Abbrev", 20)
) << Set Field Width(10);
date = today_date;
While(date >= start_of_month,
{percent_nok_ict, percent_nok_fct} = TOP5:calculate_nok_percentages_for_date(dt, date);
dt_results << Add Rows(
{Format(date, "yyyy-mm-dd"), percent_nok_ict, percent_nok_fct}
);
date = Date Increment(date, "day", -1);
);
dt_results;
);
TOP5:calculate_nok_percentages_for_date = Function({dt, specific_date},
{start_of_month, today_date} = TOP5:get_dates_percent();
dt_top5 = dt << Summary(
Group(:Date, :Nom de fichier, :Ref Produit, :ProductName, :Baie, :ICT_FCT, :Result, :Reason Error, :CodeError),
Freq("Aucun(e)"),
Weight("Aucun(e)"),
invisible
);
nameFilesNok = Associative Array(Column(dt_top5, "Nom de fichier") << Get Values) << Get Keys;
total_nb_total_ict = 0;
total_nb_total_fct = 0;
total_nb_nok_ict = 0;
total_nb_nok_fct = 0;
For(k = 1, k <= N Items(nameFilesNok), k++,
FileRows = dt_top5 << get rows where(
:File Name == nameFilesNok[k] & :Date >= start_of_month & :Date <= specific_date & Contains(:File Name, "GOLDEN") == 0
);
nb_total_ict = 0;
nb_total_fct = 0;
nb_nok_ict = 0;
nb_nok_fct = 0;
total_rows = N Items(FileRows);
For(j = 1, j <= total_rows, j++,
If(dt_top5:ICT_FCT[FileRows[j]] == "ICT",
nb_total_ict = nb_total_ict + dt_top5:Nb. de lignes[FileRows[j]];
If(dt_top5:Result[FileRows[j]] == "NOK",
nb_nok_ict = nb_nok_ict + dt_top5:Nb. de lignes[FileRows[j]]
);
, dt_top5:ICT_FCT[FileRows[j]] == "FCT",
nb_total_fct = nb_total_fct + dt_top5:Nb. de lignes[FileRows[j]];
If(dt_top5:Result[FileRows[j]] == "NOK",
nb_nok_fct = nb_nok_fct + dt_top5:Nb. de lignes[FileRows[j]]
);
)
);
total_nb_total_ict += nb_total_ict;
total_nb_total_fct += nb_total_fct;
total_nb_nok_ict += nb_nok_ict;
total_nb_nok_fct += nb_nok_fct;
);
percent_nok_ict = (total_nb_nok_ict / total_nb_total_ict) * 100;
percent_nok_fct = (total_nb_nok_fct / total_nb_total_fct) * 100;
Eval List({percent_nok_ict, percent_nok_fct});
);
thank you in advance.
Your dates are numeric value if you mean that
Names Default To Here(1);
dt = Open("$DOWNLOADS/TableData SVI PY.jmp");
vals = Column(dt, "Date") << get values;
show(vals);
It could be the Date() function which is causing issues (I'm not sure as I don't know how you have defined it).