Yes, Table calc is always headache to me.
Could you check attached workbook meets your request or not.
if index() <= [Top N Sub-Category] or last()=0 then "show" else "hide" end
[Set In Out]
if [Index] <= [Top N Sub-Category] then "IN" else "OUT" end
if index() <=[Top N Sub-Category] then sum([Sales])
ELSE window_sum(if index()> [Top N Sub-Category] then sum([Sales]) end)
if [Index] <= [Top N Sub-Category] then attr([Sub-Category]) else "Other" end
Sort on 2013
Sort on 2014
Sort Set_SM_9.3.twbx 1.3 MB
Thank you for taking a look at this. I think we are close, but we're not quite there.
When I opened your attachment, I tried to sort on order date = "2013" and I would expect the following results:
As you can see, "machines" should be in the view and 'accessories' should not.
Thank you for your time!
Looks like we need to use parameter to determine "sort year".
sum(if year([Order Date (Years)])=year([Order Date (Years) Parameter])
then [Sales] end)
Initially I thought it worked. I did sort by year and then add index, it showed correctly, but when I change the sort year, It brings back to original rule of sorting by sales by entire time horizon.
Now I needed to specify which year's data I want to use as sort value to change the display sub-category dynamically....
Sort Set_SM_9.3_V2.twbx 1.3 MB