Subscribe Bookmark RSS Feed

Comparing different time slices filtering by local data filter or better with jsl

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

I have a data set of guest feedback and satisfaction % (CSI) for the last three years.

I am continuously comparing the years, same month of different years, same week of different years which is not a problem by using local data filter but I could not figure out how to:

  • Compare last month with the whole year (CSI)  (April 2015 with March 2012-March 2015), comparing last 3 months with the whole data till the last three weeks...etc...
  • Compare the holiday season and the calendar for muslims use moon calendar which makes the same holiday move 11 days bakcwards each day which means I have to compare 16.08.2015-23.08.2015 with 28.08.2014-04.09.2014 and 08.09.2013-15.09.2013...and so on

Thank you in advance for your ides and your help

1 REPLY
billw_jmp

Staff

Joined:

Jul 2, 2014

I very recently ran across some formulas that one of my JMP colleagues, Byron Wingerd, used to break out some dates by Year, Month, Week, Season....  You may find these helpful.  You will need to create some new formula columns for breakouts.

Taken from the date

Year -    Year( :Date )

Month -    Month( :Date )

Week -    Week Of Year( :Date )

Season -    Year( :Date + In Weeks( 26 ) )

Week of Season -    Week Of Year( :Date + In Weeks( 26 ) ) - 1

Year and Week -    Char( :Year ) || " - " || Char( :Week )

Week over Week Change -

If( Row() == 1,

.,

If( :Location == Lag( :Location, 1 ),

  (:Trend - Lag( :Trend, 1 )) / :Trend,

  .

)

)


Copy and paste the jsl (bolded type) into a column formula and apply to get the desired output.  For the week over week change you will need to supply/create a location and trend column.

Hope this helps.

Bill