1 Reply Latest reply on Feb 16, 2016 5:51 PM by patrick.byrne.0

    Action Filters with table Calculations

    Douglas Diamant

      I have a worksheet that compares for 16 different regions actual year to date, month to date, quarter to date, & day vs prior year based upon the date parameter a user selects.  I accomplish this by bringing in all rows & using a lookback (Last function) of a specific # of days based upon the parameter selected.  I have a second table that does the same thing - except evaluates 8 marketing channels rather than the 16 Regions.  I use different lookbacks at the channel level (8 vs 16 rows per parameter).  Prior period is a table calculation in both instances, based upon the lookback.  Both views are shown on one dashboard


      A request has com in to allow for an action filter so that when a user clicks on a Region, the Channel dash filters out values only for the given region.  Similarly, when a user clicks on a Channel, the Region table shows only values for the given channel selected.  For example:


      User selects US Region -  Channel Table shows Online & offline values for US Only. 

      User selects Radio Channel, Region Table shows Radio sales only for all regions.


      Since the prior period NC totals & variance calce are table calcs in all instances, I am running into errors.  I need this to be a table calc, however, because the lookback window varies depending upon the Parameter a user selects.


      Can someone advise?



        • 1. Re: Action Filters with table Calculations



          If I understand correctly, there is a need to utilize the dashboard actions but to not have them manipulate the data in a way that will disrupt calculations being calculated on the table. Now, there is no way to lock these from happening. What I would suggest is building a dashboard that has your totals and other stable(there exists a need to not have these changed) calculations into one worksheet and then add that to a dashboard. Then using an additional worksheet that could be manipulated by the end user, that would change the data on another section of the dashboard.


          Here is another response I had written that might include some additional helpful information in regards to the above stated issue.



          As well if you could provide a sample packaged workbook I could examine your situation more closely:

          A sample packaged workbook (.twbx). If the data is confidential, a sample packaged workbook with sample data that mimics the structure of the original workbook is sufficient. Instructions for creating and sending a packaged workbook can be found at the following link: