Thanks for this - A few questions came up when I was trying to replicate this:
1) The "last 3 months" formula - I want to capture just one month before and one month after the "Order month", and tried to add this to the formula (middle portion), however, it did not work so I must be interpreting it incorrectly:
"datetrunc('month',[Order Date])>=(dateadd('month',-1,datetrunc('month',[Orders Month])))
datetrunc('month',[Order Date])>=(dateadd('month',1,datetrunc('month',[Orders Month])))
datetrunc('month',[Order Date])<=datetrunc('month',[Orders Month])"
I actually had a formula which works on my own data but it seems that it doesn't work on the dummy data. I am trying to say, "take Dec'14 data, take the order month -1, order month +1, and the order month". Any idea why this does not work?
DATEDIFF('month', [Order Date], #2014-12-01#) = 0 OR
DATEDIFF('month', [Order Date], [Order Month]) = -1 OR
DATEDIFF('month', [Order Date], [Order Month]) = 1 OR
DATEDIFF('month', [Order Date], [Order Month]) =0
THEN "Show" ELSE "Hide" END"
2) What exactly is the "In the month" formula doing?
"if datetrunc('month',[Orders Month])=datetrunc('month',[Order Date]) then [Sales] end"
I can see that if I change the Product Name filter to be based on "Sales" rather than "In month sales", the data changes. I interpret the formula as saying, "if the month of the order month = the month of the order date, then show the sales" - looking at how the data changes between the two, it looks like it is focusing the Top N sort on the month, but I can't see this from the formula?
3) What if I want to add Category or Segment into the mix? I was using an INDEX() Advanced in order to get the Top N by Category - I used the "Use Advanced Options in the Calculation (Alternative Example)" (http://kb.tableau.com/articles/knowledgebase/finding-top-n-within-category)
Thanks a lot,
it would probably be easier to sit down over the phone for this
1. you formula will always return false because a date cannot both be more than 1 month in the future and less than this month, i have included the correct calculation in the attached workbook.
2.this formula is used to isolate the single months sales for the purpose of sorting and then filtering, it means the 1 month either side is ignored when it comes to calculating the index
3. This depends on how complicated you want to get, in the attached workbook i have shown you how to switch between dimensions using a parameter, this works fine if you want to look at sub category or product or segment, but if you want top n products per sub category there is a different mechanism we need to use as the basis of the filter is 'table down' in this example therefore if we brought in products and sub category you'd get the top 5 products from the first category and not below. To do this we would create a second sheet and depending on the parameter selection we would 'show' or 'hide' the relevant sheets.
Hope this helps.
Nicola Prime.twbx 359.5 KB
Sorry for the delayed response, something else came up that needed my attention.
This is great! Thanks a lot for this - finally I can I present my data in the way I need!