I need your help with the next JMP Challenge!

DonMcCormack

As part of the next challenge, I'd like to ask participants to create hard to build column formulas. For example, I was recently asked by a customer how they could identify week of the year where weeks ran Monday-Sunday and the first week ran from 1/1 to the first Sunday of the year (i.e., if the year started on a Friday, week 1 would only have 3 days: F, Sat, Sun). Turns out to be more challenging than it sounds! I'd love to hear about the challenging formulas you've come across. They can sound simple or be complicated. Or, they may get a JMP table to do something that you wouldn't think possible. I am open to anything that will test people's JMP skills. Post them here or send them to me via email.

Thanks, Don

wendytseng

This is not nearly as hard as the one you described but I have had 2 customers recently ask to do this:

 

Create a column that is the datetime difference (let's say days) row-over-row but within a group (another column...let's say it is Product).

My solution was to use Tables --> Sort to order asc by the datetime and the grouping column.  Then make the grouping column the "Group By".  Then lag the datetime.  Then subtract the datetime from the lagged date time.