topic Re: Summary Table - Getting rows for each group combinations, including empty ones in Discussions

Summary Table - Getting rows for each group combinations, including empty ones

STA — Sat, 10 Jun 2023 23:49:27 GMT

Hello,

I am writing on of my first scripts in JMP, and would like to get a nice chart of the proportion of events per months. I am using a summary table to get the sum of quantities of events per month, but there are some quiet months for which I do not have any data points.

How could I get the empty months to till be displayed in my chart?

For example in the two charts below, I would like the left one to display an empty position for March 2022 and the right one to display empty position from January to October 2020.

I am using JMP version 16.0.0

Thanks a lot for your help!

Samuel

Re: Summary Table - Getting rows for each group combinations, including empty ones

jthi — Mon, 13 Jun 2022 19:11:05 GMT

Are you using label nesting or how are you building that graph builder? Is your X-axis in numeric continuous (and date formatted)?

Re: Summary Table - Getting rows for each group combinations, including empty ones

STA — Tue, 14 Jun 2022 06:30:36 GMT

Hi Jthi,

Thanks for your reply! I do not see the Month Year formula in the Date Time formula list, do I have to combine formulas to get it?

My values in the calculated column are indeed set as numeric and continuous, but not formatted as date as so far I have one column for the month and another one for the years.

Best regards,

Samuel

Re: Summary Table - Getting rows for each group combinations, including empty ones

jthi — Tue, 14 Jun 2022 06:34:33 GMT

Right-click on date column and create new formula column. JMP is able to offer some pre-defined columns for you and Month Year is one of them.

Re: Summary Table - Getting rows for each group combinations, including empty ones

STA — Tue, 14 Jun 2022 08:27:10 GMT

Hi Jthi,

Thanks for your quick answer. I do now manage to get the empty periods for the graphs, but with the same data set the empty periods are removed from the P-chart.

I anonymized the data, here are the charts I have at the moment:

Best regards,

Samuel

Re: Summary Table - Getting rows for each group combinations, including empty ones

jthi — Tue, 14 Jun 2022 14:11:55 GMT

If you need them in P-Chart I think you might have to use "Date Hierarchy table" and use that for joining.

Create new table and set columns datatype to date type. Then initialize with by using Initialize Data. Set From date to first date you have in your data (or even earlier) and same for To. Use step of 1 month.

Then use New Formula to create the Month Year column and use that to join to the original data you have (you need to use left/right outer join). After joining correctly you should have some rows which have only the month year and rest columns empty. Hopefully then you can plot the P-Chart and have some points missing.