4 Replies Latest reply: Nov 20, 2015 5:48 AM by Danielle van Eeden RSS

    Compare 2 Months Selected by User

    Danielle van Eeden

      Hi All,

       

      I currently have a pivot table which compares factory statuses between 2 months - based on the month selected in the calendar and the month previous to the selected month. The table only shows records where the factory status differs between the 2 months, e.g.;

      The Previous Month expression (i.e. Mar-2015 in this example) is 

       

      =if(concat( {$<Month=, Year=, MonthYear = {"$(=Date(addmonths(max(MonthYear), -1), 'MMM-YYYY'))"}, [Factory Status] = >}DISTINCT [Factory Status], ', ')<>

      (concat( {$<MonthYear={"$(=max(MonthYear))"}, [Factory Status] = >}DISTINCT [Factory Status], ', ')),

      concat( {$<Month=, Year=, MonthYear = {"$(=Date(addmonths(max(MonthYear), -1), 'MMM-YYYY'))"}, [Factory Status] = >}DISTINCT [Factory Status], ', '))

       

      The Current Month expression (i.e. Apr-2015 in this example) is

       

      =if((concat( {$<MonthYear={"$(=max(MonthYear))"}, [Factory Status] = >}DISTINCT [Factory Status], ', '))

      <>

      (concat( {$<Month=, Year=, MonthYear = {"$(=Date(addmonths(max(MonthYear), -1), 'MMM-YYYY'))"}, [Factory Status] = >}DISTINCT [Factory Status], ', ')),

      (concat( {$<MonthYear={"$(=max(MonthYear))"}, [Factory Status] = >}DISTINCT [Factory Status], ', ')))

       

      However, what I now need to be able to do is to allow the user to select 2 months of their choosing to compare - so the 2 months in the table may not be consecutive months. For example, the user may want to compare factory status changes between Jan-2015 and Sep-2015.

       

      I've been wracking my brain and can't think how to do this... thought about creatin a second calendar in the script, or using variables and input boxes, but I just can't get anywhere with it!

       

      Any pointers would be hugely appreciated!

       

      Thanks,

       

      Danielle