I think that this is one way of doing what you are looking for (file attached)
Created a Calculated Field called "Include Latest Week"
IF MAX([Period Weekname Date]) = WINDOW_MAX(MAX([Period Weekname Date]) ) THEN "INCLUDE"
Then I put a filter (using Quick Filter) on "Include Latest Week" to only show "INCLUDE".
* Only show latest week
* When data is appended look at the latest week
* When looking back the calcs still work (by selecting ALL) ; I guess you could modify it to count the number of weeks so that you only show the last couple of weeks.
* The sort already in place should work to automatically sort by #Followers descending.
I'm sure there's other (probably more elegant) ways to achieve the same effect.
All the best
View_Sort_ID20121030_PW.twbx.zip 31.9 KB
Thanks - can you explain how the sorting works, and explain what I would need to do if I wanted to sort by another variable?
Okay - so I've just had a play around with it. The INCLUDE EXLUDE thing works well - thank you for that. I guess that using "WINDOW" is different than a filter. My understanding is that a filter actually makes the data unavailable for other calculations, but "WINDOW" just chooses what to display, while making the data available, is that correct?
The sorting does not work however. I changed the file to append to make one record that was previously lower have a very high value and it is not sorted automatically. See attached new file to append and screenshot. This was not obvious in the past as my values for each record were pretty consistent. Let me know if you have a solution for the auto-sorting and explaination.
The short answer is "I don't know" - I've been playing around with the sort (workbook attached) to see how we could get it to work and still don't know.
It maybe that the way I suggested to filter so that you only show the latest week won't work with the sort to only sort that week (I'll put a caveat in here that you probably can get it to work with a clever Table Calculation - but I'm not that Clever ).
I'll summarize what I have attempted ... so someone else maybe able to pick up the baton.
Original - I appended the data and created another dimension of status so that I could turn on/off the appended items. Highlight Joseph Pirraglia so that it stood out. As you said the sort doesn't work. I didn't change the sort from your Original workbook - how did you apply the sort ? (see screenshot attached of sort criteria)
Bar Chart - I replicated the sheet using a Bar chart rather than text table - I applied a filter on name to sort Descending by Number of Followers. If I toggle the data to show appended data then the sort moves up and down (but only on the Grand Total (sum of all the weeks). Simple I thought just add the original include / exclude filer to show the week and it will work - - - Ouch ! If I try to add that filter then everything is showing as Include ???? so the filter doesn't work on the barchart !?!.
OK - start building up from scratch
Attempt 1 - just using Number of Followers put in Include / Exclude Filter for Latest week on Text Table
Attempt 2 - copy of Attempt 1 and apply sort - this sorts on the total (sum of all weeks)
Attempt 3 - copy of Attempt 2 added Grand Total to confirm assumption of sort order.
Attempt 4 - copy of Attempt 2. Using the "Show Me" button change the text table to a horizontal bar chart. (I did this because I understand that if you use the Show Me button then it locks in your filters)
Attempt 5 - Put the sort in on Name, Descending by Sum of Followers.
Attempt 6 - using Show Me change back to Text Table
Attempt 4 proves that the filter can be applied to the bar chart by using Show Me.
In Attempt 5 & 6 the sort only works correctly if you select the Appended items, other than that it sorts the sum of all followers. But if you do this then you don't get the % Diff Followers.
Not sure if this helps - but at least you know I looked at it.
All the best
I came across the KB article on nested sorting and thought that it would get around the sorting issue on this thread.
I did a quick check and it appears to work.
If you look at the Original worksheet on the attached workbook.
I have created a set with the Name and Period Weekname and then sorted on that, and then deselected 'Show Header' as per KB article.
You can change the Status on the Quickfilter from original to appended to simulate appending new records.
Hope this answers your question.
That works great. Thanks for coming back and following up on this one. Exactly what I needed.