Register
Log In
Unanswered
Home
July 11, 2016
Offline
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
Dashboards
June 25, 2016
Offline
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
November 8, 2013
Offline
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.
July 11, 2016
Offline
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.
Dashboards
June 25, 2016
Offline
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
Dashboards
June 25, 2016
Offline
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
Most Users Ever Online: 57
Currently Online: Meyanui
16 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
