topic Re: cumulative of only new item (exclude repeated) by weekly in Discussions

cumulative of only new item (exclude repeated) by weekly

dadawasozo — Thu, 08 Jun 2023 16:41:08 GMT

Hi,

I want to build a graph that will show cumulative of only new items by week from a table that has 2 columns (see attached as example). In the table, there are 91 different type of fruits that link to the date which is from 01/02/2024 - 9/29/2024. Below is the desired graph. The steps so far I took to get the graph seems not too effective/efficient. I wonder if anyone has more effective (JSL) way to do it?

Steps I used to generate the graph:

1) create a column in the same table with Formula(Week Of Year(:Date)).

2) summarize table N(Fruit) by Fruit and WW

3) in summary table of 2) , add a column (Count of WW) with Formula(Col Min(:WW, :Fruit))

4) summary table Mean(Count of WW) by Fruit

5) summary table N(Mean(Count of WW)) by Mean(Count of WW). I then manually add rows with value = 0 to any missing WW because those week has no new fruit. This is the table then I used to build the graph using statistic = cumulative.

Re: cumulative of only new item (exclude repeated) by weekly

txnelson — Thu, 26 Jan 2023 08:23:36 GMT

All I did to create the JSL for your chart, was to step through your stated steps, and copy the JSL from the log that JMP produced. I did add a little original code to solve the adding of zeros in for any work weeks where there vere no new fruits, but other than that, JMP created the JSL, not me

names default to here(1);
dt=data table("Fruit by date");
// New column: Column 3
Data Table( "Fruit by date" ) << New Column( "WW",
	Numeric,
	"ordinal",
	Format( "Best", 12 ),
	formula(week of year(:date))
);
// Data table summary
// → Data Table( "Fruit by date (1) By (Fruit, WW)" )
Data Table( "Fruit by date" ) << Summary(
	Group( :Fruit, :WW ),
	N( :Fruit ),
	Freq( "None" ),
	Weight( "None" )
);
// Change column formula: Count of WW
Data Table( "Fruit by date By (Fruit, WW)")<<
New column("Count of WW",  Formula( Col Minimum( :WW, :Fruit ) ));

// Data table summary
// → Data Table( "Fruit by date By (Fruit, WW) By (Fruit)" )
Data Table( "Fruit by date By (Fruit, WW)" ) <<
Summary( Group( :Fruit ), Mean( :Count of WW ), Freq( "None" ), Weight( "None" ) );

// Data table summary
// → Data Table( "Fruit by date By (Fruit, WW) By (Fruit) By (Mean(Count of WW))" )
Data Table( "Fruit by date By (Fruit, WW) By (Fruit)" ) <<
Summary(
	Group( :"Mean(Count of WW)"n ),
	N( :"Mean(Count of WW)"n ),
	Freq( "None" ),
	Weight( "None" )
);

// Data table summary
// → Data Table( "Fruit by date By (WW)" )
Data Table( "Fruit by date" ) << Summary(
	Group( :WW ),
	Freq( "None" ),
	Weight( "None" ),
	link to original data table(0)
);

for each row(
	:N Rows = 0;
);

:WW << set name("Mean(Count of WW)");

// Update data tables
Data Table( "Fruit by date By (WW)" ) << Update(
	With( Data Table( "Fruit by date By (Fruit, WW) By (Fruit) By (Mean(Count of WW))" ) ),
	Match Columns( :"Mean(Count of WW)"n = :"Mean(Count of WW)"n )
);

data table("Fruit by date by (WW)") << Graph Builder(
	Size( 891, 456 ),
	Show Control Panel( 0 ),
	Variables( X( :"Mean(Count of WW)"n ), Y( :N Rows ) ),
	Elements(
		Bar(
			X,
			Y,
			Legend( 5 ),
			Summary Statistic( "Cumulative Sum" ),
			Label( "Label by Value" )
		)
	)
)

Re: cumulative of only new item (exclude repeated) by weekly

dadawasozo — Thu, 26 Jan 2023 12:45:50 GMT

Hi Jim

Thanks, is there shorter way to get to the graph? Or I have no choice must go through the steps?

I will have a very large data that is from daily for months and concern of if these steps are the method to deliver the output later.

Re: cumulative of only new item (exclude repeated) by weekly

txnelson — Thu, 26 Jan 2023 14:05:51 GMT

JMP can handle data tables wit billions of rows, so if you have just an average sized computer, you will not have an issue.