Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Use comparative analysis. This way no need to create another calendar.
Regards,
Miskin
Hi,
Use comparative analysis. This way no need to create another calendar.
Regards,
Miskin
Hi,
I believe comparative analysis (Using Comparestate for same fields) can help you to compare the two months.
Thanks.
Hi, it's maybe helpfull
Thanks all for your quick responses!
Well on my way to completing this task now using comparative analysis, which I had not discovered before!