Comparing different time slices filtering by local data filter or better with jsl
May 13, 2015 4:53 AM(1506 views)
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
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.