Slicer to switch between months and weeks with same amount of bars in a barchart
Hello,
we are currently building a dashboard that uses a global parameter as slicer to switch between months and weeks. The goal is to have some equal amount of bars in a barchart. For example, when initally loading the discover within the present with a slicer set to months and looking back one year resulting in 12 bars, switching to weeks should show the last 12 weeks.
It does show the last year instead resulting in 52 weeks, however.
As discover categories, we use a custom column that picks the year and month in case the global parameter is set to months ("m") or weeknumber otherwise:
The filter in the discover is set to a custom column filtering down the last 12 items. When the global parameter is set to months, the custom column contains the last 12 months otherwise last 12 weeks:
How can we make sure that switches and slices interactively done in the present also updates the available amount of data in the background? This also affects slicing within the discover.
Thanks,
Fabian
5 replies
-
Hi
My first tip would be doing this with a minitab in present.
create two discovers - one with last 12 week, the other with the last 12 moths, put them in a mini tab and witch based on the parameter.
If this solution is enough it will also be easier to maintain I believe. -
While I would use the mini tabs 9/10, if there is a significant requirement for this to work that way - here's what you can do:
The key problem here is that you have 2 different column with 2 different natural sorting attributes, and the sort for a custom column cannot be dynamic.
Therefore you need to find a way to sort the column while creating the list formula. In such a case, sorting by the minimal date in each week/month should work.
try the following:
1. create the custom column, the way you created it.
2. Create a list with the following code:
{Tail(Order({AllMembers([custom_attributes].+[custom column ID])},(min([data].[dateKey])),asc),12)}You need to replace the custom column ID with your custom column, and the [data].[dateKey] with your own date column.
This will sort all the elements in your custom column (so either all weeks or all months) and filter the latest 12 (because they are sorted by the minimal date, and filtered to the last 12).
I hope this helps, although again, I would use the mini tabs -
Hi,
two inspiring ideas to solve this - THANK YOU. But I still wonder why it should have something to do with sorting... As far as I understand it seems to be a problem with the refreshing of the Custom Column slicer which holds the 12 last months or 12 last weeks.
has a toggle button which perfectly switches between months and weeks, i.e. the categories of the column chart change when the button is clicked - fine.
This is the starting point: The toggle button on the right shows "Monate" (= months) which is true for the categories of column chart as well. 202402 to 202501 are the last 12 (full) months.
When clicking on the toggle button so that it switches to "Wochen" (= weeks), again the categories of the column chart react accordingly and show weeks now. But - 202405 to 202505 (x-axis labels are a little bit shifted) are all the weeks of the last 12 months, and not the last 12 weeks, as intended!
The Custom Column slicer at the top of the screen, showing "12 Selected", internally holds a Month Sequence (-1, -2,... -12) or a Week Sequence (-1, -2,... -12). We checked, that this slicer reacts to the toggle button accordingly, i.e. at the time of the second screenshot it actually holds the Week Sequence, which is correct as the toggle button was switched to "Wochen".
So, for me it looks like a refresh is missing: When I manually select a 13th element in the slicer, manually refresh, deselect the 13th element and refresh again then I see the last 12 weeks. I played around with the slicer settings ("Auto Run", "Reset Selection on Cascade") but that did not show an effect.
THANKS & best regards,
Michael