7 Replies Latest reply on Nov 22, 2012 5:05 PM by Nathan Schofield

    View last week and sort

    Nathan Schofield

      Hi All,


      I have a workbook (attached example with dummy data) which contains data for multiple periods, and to which I will be continually appending new periods of data for. I want a view that defaults to the latest period. This view also has calculations that look back 1 period.


      I have attached a workbook (View_Sort.twbx), base data(Table_View_and_Sort.csv), and 1 weeks data to append (Table_View_and_Sort_to_append.csv).


      This is the solution I am looking for:

      • Similar view to what already exists in the workbook, but for the latest week only.
      • That when data is appended, the book defaults to the latest week.
      • That the calculation which looks back 1x period still works
      • That the week in view (latest week) is automatically sorted by #Followers descending


      Would appreciate any help I can get.



      Nathan Schofield

        • 1. Re: View last week and sort
          Ian Devonald

          Hi Nathan


          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"

          ELSE "EXCLUDE"



          Then I put a filter (using Quick Filter) on "Include Latest Week" to only show "INCLUDE".


          This should

          * 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


          • 2. Re: View last week and sort
            Nathan Schofield

            Thanks - can you explain how the sorting works, and explain what I would need to do if I wanted to sort by another variable?

            • 3. Re: View last week and sort
              Nathan Schofield

              Apologies - there was some problems with my


              file, new one attached.

              • 4. Re: View last week and sort
                Nathan Schofield

                Hi Ian,


                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.





                • 5. Re: View last week and sort
                  Ian Devonald

                  Hi Nathan,

                  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


                  • 6. Re: View last week and sort
                    Ian Devonald

                    Hi Nathan


                    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.

                    Best regards


                    • 7. Re: View last week and sort
                      Nathan Schofield

                      Hi Ian,


                      That works great. Thanks for coming back and following up on this one. Exactly what I needed.



                      Nathan Schofield