3 Replies Latest reply on Dec 31, 2013 2:46 PM by Jonathan Drummey

    Table calculation from broader time access than table?

    Neil Berkowitz

      I have been asked to produce a visualization that counts the number of months out the past 12 months that meet a particular condition. But they want the vizualization to only show periods that may be shorter than 12 months. There are also institutional reasons why I cannot just query the data as an additional source or table. So:

      1. Is there a way to force the table calc to a period of 12 months regardless of the number of months displayed?
      2. Is there a way to obtain the value from a table calc on a different worksheet, ideally a hiddent one?
      3. Are there suggestions for any other approaches to explore?

       

      Thanks

       

      Neil Berkowitz


        • 1. Re: Table calculation from broader time access than table?
          Joshua Milligan

          Neil,

           

          If I understand your question: you want to be able to show the last 12 months (from the current month or from the last month in the data?).  Sometimes there may be less than 12 months present in the data (e.g. there might only be 6 months worth of data), but you still want to show the last 12 months, even if the first 6 of those months have blank values.

           

          There are a few options:

          1. Date fields can be set to "Show Missing Values", in which case you will have the MIN and MAX dates from the source (in any context you define) and missing values will be shown.  Table calculations can be used to address the padded cells in this case.
          2. You could blend, using a full set of dates as the primary source.

           

          There are some issues:

          1. Obviously, you would have to have a Min and Max that form the bounds of the range you want.  You could potentially use a Custom SQL statement to union in a single row of dummy data that is 12 months prior to the last date.

           

          2. The level of detail will be constrained by the dimensions present in the primary data source.  If you only want aggregation at a month level, you're fine.  However, if you want other dimensions to define the level of detail (e.g. department, employee, branch, etc...), then the full domain of values for each of those dimensions will need to be cross-joined with the list of dates to give you a complete combination of all dimension values for blending.  Depending on how many dimensions and the cardinality, this can very quickly become an untenable solution.

           

          As far as obtaining the value of a table calc on another worksheet: I'm afraid not.  The table calculation is calculated based on the active fields in a given worksheet and cannot be referenced elsewhere.

           

          Feel free to mock-up an example and attach a packaged workbook if you'd like to explore either of the above options (or perhaps other possibilities too!)

           

          Happy New Year!

          Joshua

          • 2. Re: Table calculation from broader time access than table?
            Jatin Saini

            Hi Neil,

            You will do it by calculations as: SUM(IF  {condition} count(Month)<12 Then 1 else 0).

            or

            You can do it by doing cross tab copy of all the meeting data according to your conditions.

            • 3. Re: Table calculation from broader time access than table?
              Jonathan Drummey

              Joshua wrote:

               

              > As far as obtaining the value of a table calc on another worksheet: I'm afraid not.  The table calculation is calculated based on the active fields in a given worksheet and cannot be referenced elsewhere.

               

              That doesn't mean we can't do it all in a single worksheet/view, though. Within a single view, table calculations can be nested, and table calculation filters are applied after most all computations (including table calculations) are complete. So one option is to build the view with the finest level of granularity necessary to build the view, then aggregate at higher level(s) using table calculations, and finally use a table calculation filter to hide what's not needed for the final view.

               

              Like Joshua said, if you want more help on this then posting a packaged workbook with some sample data would be helpful.

               

              Happy New Year!