Hi Catalin,
Possible to have multiple slicers item group into one slicer item? I need to have several slicers item categorized with groups of months/year name-1,3,6,9,12 months.
eg:a) SlicerItem1(3 months) - Jan-17,Feb-17,Mar-17 (group into 1 sliceritem instead of stated 3 months in 3 sliceritem)
b) SlicerItem2(6 months)- Oct-16,Nov-16,Dec-16,Jan-17,Feb-17,Mar-17 (group into 1 sliceritem)
c) SlicerItem3(9 months)- Jul-16,Aug-16,Sep-16,Oct-16,Nov-16,Dec-16,Jan-17,Feb-17,Mar-17 (group into 1 sliceritem)
And then another main issue is i want each time i refresh the pivot table, each of the different sliceritem can auto refresh by detect the latest month(eg:Apr-17) from the data to grab the months name correctly on the 1,3,6,9,12 months' sliceritem.
I tried to research on Power BI and Power Query, and i still have no clue on how to do this. May i know which method is suitable? Or VBA?
Thanks!
Regards,
Hooi Thin
Hi Hooi Thin
See if this helps. It will only work if you have unique months/year as the date field.
I am unable to hide the blank in the slicer as I am using Excel 2010 but I believe you can do this in Excel 2013.
Any addition to the data, just refresh and the slicer will pick up the latest months.
Sunny
Hi Hooi,
Another option is to use Quarters. Use a formula like this one to create quarters from a date column:
You should also have a year slicer. I you want to see 3 month data (each quarter has 3 month), select the year and the desired Quarter. If you want to see 6 month data, select Quarter 1 and 2 in the Quarter slicer. For 9 month, select Q1,2,3. For 12 month data, clear the quarter slicer and use only the Year slicer.
Sample file attached, I slightly modified Sunny's sample.
Hi Sunny/Catalin,
Thanks for both help.
I am sorry that i should attached the desire sample result for clearer concept. Please refer to sheet 2.Apologize that abit misunderstanding from the real one.
Instead of putting quarter, customer would like to see by months which is click by only one button with 1 or 3 or 6 or 9 or 12 months.
The existing report was using dropdownlist by macro code to perform the filter, i plan to enhance it now using slicer concept(5 slicer items in one slicer)
The report is based on previous month as latest date, so every month will need to refresh data and slicer as well.
Thanks again if you could help.
Hi Hooi Thin
Your request is very tough to do and very challenging. I have somehow did something like what you needed but it is quite complex.
I have added comments in the file and hope you can understand how I did it.
Sunny
Hi Hooi Thin
Please ignore my remarks about hiding the blank caption slicer using Excel 2013. It won't work in this situation.Although the caption is blank, there is data.
I suggest to modify the formula in the Helper Column to =IFERROR(VLOOKUP(EOMONTH([@Month],0),Table!$A$2:$A$13,1,FALSE),"Others")
Please note that you cannot use MIN(Table!$A$2:$A$13) or MAX(Table!$A$2:$A$13) to replace "Others" (how I wish it could work
) as it will affect the filtering.
Hope this helps.
Sunny
