October 12, 2017 at 4:25 pm #444FrankKeymaster
- Topics Started - 56
- Replies Written - 48
We got a question on how to see the rest of the months in the year, starting with the current month. Easy enough to select all the months, including the current month in a filter…..but…..what happens as we roll thru the year, or when next year comes? Instead of selecting specific months for your filter, that you may need to reselect each month the following technique might work for you.
Let’s start with the idea – we want to see Sales and Sales Plan information for the current month thru the rest of the year, by month. So as each month passes we’ll loose a month on the display since there are fewer months left in the year. Of course we want the View to do this automatically so we don’t have to change the filter year to year and month to month.
Stratum allows us to do this pretty easily with a couple of dimension filters.
In our view we’re going to have YearBasedMonthsBased in in the columns (or a similar Based time dimension). This allows me to apply a filter to Year, which will just simply be the Current Year. On the Months instead of a normal member filter we are going to select an expression filter – this allows us to apply more complex filtering logic, beyond simple member selection. Below is the actual expression we used. You might need to tweak it to fit your exact calendar, but the example should be ok:
// Only show selected months
([Time].[Year Based Months Based].CurrentMember.name = “Current Month” OR
[Time].[Year Based Months Based].CurrentMember.name = “Next Month” OR
(INSTR(1, [Time].[Year Based Months Based].CurrentMember.name, “Out”)<>0))
As you can see the first two lines select the Current Month and Next Month – these are pretty straight forward. The 3rd line uses the INSTR to search the member name for the word Out. This is because all our months that are beyond the current month and the next month are labeled as 2 Months Out, 3 Months Out, 4 Months Out….etc. Yours may be use something different, just search for a common word. The cool part of this is that as we move thru the year this filter only pulls in the remaining months. You could do this for only months prior to the current month as well by searching for different strings – in our case to get prior months we would of searched for Previous Month and the word Ago – our months are labeled 2 Months Ago, 3 Months Ago…etc. In either case we only get months in the Current Year since we’ve placed a filter on the Year for Current Year.
In the view below you can see that we have information for the current month (which is Sept) thru the end of the year. We’ve included a bunch of data from the Current Year so the user see orders, a baseline sales plan and an adjustment column. We’ve also included Last Years sales – which is a different calc that I may post on at a later date.
Here’s the end result, a view that shows only the current month thru the end of the year.
- This topic was modified 2 years, 4 months ago by Frank.
You must be logged in to reply to this topic.