2 Replies Latest reply on Jul 23, 2013 9:16 AM by eraufars

    Use result from sheet1-table to sheet2-plot? (with TWBX and screenshot)


      I have a series of numbers and I was able to do some aggregation calculations on them using a table where I successively add another calculated value based on the previous values in the table.


      In the final stage, I want to make a plot with only the last result. So my thought was if I can create a new sheet with the plot, and get the values from the calculation sheet which is just a big number table?


      Another possibility would be if I could make a calculated value that includes all the other calculations and aggregations somehow, so that I could put that value 'independently' in a table (or anywhere), but I havent been able to do that without building a big table of multiple measures/calc fields.



      The sample workbook illustrates this.

      Column 1: There's a series of numbers ordered by index.

      Column 2: The running total. Since numbers can be positive or negative, so can the RT.

      Column 3,4: Extreme values of the running total. This is what I want to plot in another sheet.


      For some simpler aggregations such as sum, average and others, I have been able to use window_X functions to produce a calculated value that in itself contains everything, but for the above example I haven't. I depend on the table to get my result number but I don't know how to bring it out of there.


      A great thing would be if I could keep this worksheet in the background for an admin/advanced user to view, and the plot only for public view where the details are not shown.

        • 1. Re: Use result from sheet1-table to sheet2-plot? (with TWBX and screenshot)
          Jim Wahl

          If your goal is produce something like this (either as a table or graph):

          2013-07-19 14-15-39.png

          you've already done 99% of this with your sheet 1.


          Your table calcs for Window Max/Min are calculating the correct value, if you set compute using is set to Index. In sheet 1, compute using Index and compute using Table (down) are equivalent, but once you move Index from the rows shelf to the detail shelf, you'll need to specify it.

          Window_max (RUNNING_SUM(SUM([Value])))


          It's good practice to specify the default compute using dimension in the calculated field (enter/edit) dialog box---click on "Default Table Calculation" in the upper right corner.


          And, since you only want one value, these table calcs should be wrapped in a

          IF FIRST() == 0 THEN

              [your table calc statement here]



          Now you can create a new sheet.

          1. Put index on the level of Detail shelf, measure names on the Rows shelf, measure values on the Text shelf.
          2. Remove the measures you don't want.
          3. Double-check the compute using for the table calcs.
          4. You'll have extra white space as a result of the nulls generated by the rows where IF FIRST() != 0. Tableau v8 "stacks" these marks by default, but you can disable it by selecting Analysis > Stack marks > Off from the top menu.


          You can also use these Min/Max values in other calculations on a sheet (or tooltips, ..).



          • 2. Re: Use result from sheet1-table to sheet2-plot? (with TWBX and screenshot)

            Thank you very much!


            The answer wasn't the straight road to a direct solution, but neither was my question clear enough to be able to give a good answer.


            A while of trying out the things you described helped me reach what I wanted, a scatter plot based on the above numbers.


            This is a big step forward. Also, knowing that it can be done opens up for a lot of other interesting things.