1 Reply Latest reply on Jul 3, 2018 4:32 AM by Tim Dines

    Creating separate one-column calculated fields with dynamic filters

    Nathaniel Lerner

      I have a set of data that has a number of filters in place that can be changed by the user. Here is a template of what I would like the information to look like....

      2018Q22018Q1Difference Between CyclesDifference Between Current Cycle 1st Scenario
      One ScenarioSecond ScenarioOne ScenarioSecond ScenarioOne ScenarioSecond ScenarioOne vs. Second
      Account9Qtr Sum9Qtr Sum9Qtr Sum9Qtr Sum9Qtr Sum9Qtr Sum9Qtr Sum
      1$  5.00$   2.00$  8.00$   3.00$  (3.00)$  (1.00)$       3.00
      2$  8.00$   8.00$  9.00$   4.00$  (1.00)$    4.00$          -
      3$ 10.00$  (5.00)$ 10.00$   5.00$       - $(10.00)$     15.00
      4$  2.00$   7.00$ 11.00$   6.00$  (9.00)$    1.00$     (5.00)
      5$  8.00$   1.00$ 12.00$   7.00$  (4.00)$  (6.00)$       7.00
      6$ 15.00$   6.00$ 13.00$   8.00$    2.00$  (2.00)$       9.00
      7$ 30.00$  10.00$ 14.00$   9.00$  16.00$    1.00$     20.00
      8$  5.00$  16.00$ 15.00$  10.00$(10.00)$    6.00$    (11.00)
      9$  2.00$  12.00$ 16.00$  11.00$(14.00)$    1.00$    (10.00)
      10$  8.00$  13.00$ 17.00$  12.00$  (9.00)$    1.00$     (5.00)

            

       

      The hardest part is this is all dynamic. The user needs to be able to select as many scenarios as they want (there are about 9 different types). The user also needs to have the ability to pick which 2 cycles to compare the data too as well. I have found a way to easily make the first 4 columns in the diagram show up. I also was able to figure out how to show the differences, but sometimes it doesn't calculate correctly and the difference columns are in between in each of the scenarios rather than at the end as shown above. Any thoughts of tackling this task?

        • 1. Re: Creating separate one-column calculated fields with dynamic filters
          Tim Dines

          It would be great if you could share your workbook here for others to see exactly what you are getting at.  The calculations not coming out as expected probably means that the are being aggregated at a level other than what you are intending.  You should do a search for Level of Detail (LOD) calculations.  Just remember that when you have numbers in columns A and B and want to calculate column C from them, then you are looking at adding together sums or averages or some other operations on some sort of aggregation, rather than A+B=C you are getting SUM(A)+SUM(B)=C.  As far as making this dynamic goes, you may want to do a search for a tutorial called the Sandbox.  In the Sandbox, multiple parameters are used in order to allow the user to pick the measures and dimensions shown on the X and Y axes as well as the type of aggregation, color, etc.  This one will give you an intimate knowledge of how to work with Parameters in Tableau.

           

          I would give you the links, but we cannot copy and paste in the browser here due to security concerns.  Remember to post you sample here if possible.