10 Replies Latest reply on Nov 26, 2018 7:58 AM by Stephen Rizzo

    Data densification between two points

    Cristian Pueyo

      Hi,

       

      The situation I'm handling with is more complex in comparison with what I'm show in this post but I think with this example you'll understand better my problem to resolve.

       

      My data set is the following:

       

      Then, I'm representing this data:

      My problem comes when I filter the X dimension to show all the data between X=5 and X=20. I would like to see a line between X=5 and X=10 at Y=200 but Tableau does not show it.

      What I would like to see:

       

      I understand that Tableau is filtering data points but maybe there is a way to achieve the result I'm looking for.

       

      Thank you very much in advanced.

       

      PD: Take in account that the real situation is much more complex than the one I'm showing you, so the idea is not to solve this modifying the data set, because it could increase the data volume very much.

        • 1. Re: Data densification between two points
          Stephen Rizzo

          The issue here is that when you set your filter to X >= 5, the first point at X=1, Y=200 is filtered out, leaving X=10, Y=400 as the leftmost (first) point on the chart. The automatic axis scaling then rescales the chart appropriately.

           

          The way I see it, you need some data point at your minimum X value so that Tableau will know 1) how to scale the axis and 2) where to start drawing the line. If you don't want to touch your data set, could you change the X range control to use two parameters instead? Those parameters would then alter the first and last point in your chart, and a calculated field could determine the appropriate Y values for those points.

           

          See the attached workbook for an example (just implemented the start point to illustrate the concept).

          • 2. Re: Data densification between two points
            Cristian Pueyo

            Hi Stephen,

             

            Thank you for your answer. I'm not being able to open the Tableau file you are attaching. I'm getting the following error:

             

            Errors occurred while trying to load the workbook "C:\Users\Lenovo\Downloads\NoDensification (2).twbx". The load was not able to complete successfully.

            Error(256,66): no declaration found for element 'simple-id' (id: C:\Users\Lenovo\AppData\Local\Temp\TableauTemp\3001683298\NoDensification (2).twb) Error(256,66): attribute 'uuid' is not declared for element 'simple-id' (id: C:\Users\Lenovo\AppData\Local\Temp\TableauTemp\3001683298\NoDensification (2).twb) Error(257,14): element 'simple-id' is not allowed for content model '((cards,viewpoint?)|(viewpoints,active,device-preview))' (id: C:\Users\Lenovo\AppData\Local\Temp\TableauTemp\3001683298\NoDensification (2).twb)

            • 3. Re: Data densification between two points
              Stephen Rizzo

              That's strange - I am able to download and open the workbook without any issues. Anyways, I can summarize the changes - it wasn't too complicated:

               

              1. Open a new worksheet
              2. Create the following parameter [StartX] taking integer values and display the parameter control
              3. Create the following calculated fields:
                1. [NewX] : IF [X] = 1 THEN [StartX] ELSE [X] END
                2. [NewY] : IF [X] = 1 THEN {FIXED : MAX(IF [X] <= [StartX] THEN [Y] END)} ELSE [Y] END
              4. Add a filter on [NewX] with the condition: [NewX] >= [StartX]
              5. Add [NewX] to columns and [NewY] to rows.

               

              All this is doing is taking the first point on your chart and moving it along the line to wherever you want the start point to be.

              • 4. Re: Data densification between two points
                Cristian Pueyo

                Hi Stephen,

                 

                I checked your script and it's almost working.

                 

                For the cases where I set 'StartX' as a value that is already on the data set, the Y value that is displayed is double the reality just for this X point.

                For the rest of the cases is working perfectly.

                 

                What could it be?

                 

                Thank you,

                • 5. Re: Data densification between two points
                  Stephen Rizzo

                  It sounds like you are plotting SUM([NewY]) as opposed to just [NewY]. Try right-clicking on the [NewX] and [NewY] pills in the view to make sure that they are set to "Dimension" and "Continuous".

                  • 6. Re: Data densification between two points
                    Cristian Pueyo

                    On the real case where I want to apply your solution, Y axe belongs to cash flow, an aggregation of cash incomes and outcomes. So data must be aggregated on Y axe.

                     

                    Thank you,

                    • 7. Re: Data densification between two points
                      Stephen Rizzo

                      Then use the following calculation for [NewY] instead:

                       

                      IF MIN([X]) = 1 THEN SUM({FIXED : MAX(IF [X] <= [StartX] THEN [Y] END)}) ELSE SUM([Y]) END

                      • 8. Re: Data densification between two points
                        Cristian Pueyo

                        Hi Stephen,

                         

                        Now it's working perfect for the charts where I'm displaying a SUM of Y for each X.

                         

                        The thing is that I have some other charts where I'm displaying a Running SUM of Y along X.

                        I'm changing the formula but it's not working for all cases.

                         

                        Any light on this? Thank you very much in advanced

                        • 9. Re: Data densification between two points
                          Cristian Pueyo

                          Hi Stephen,

                           

                          I hope everything is fine.

                          Could be a way to do it?

                           

                          Otherwise I'll try to find another way.

                          • 10. Re: Data densification between two points
                            Stephen Rizzo

                            Trying to do a running sum here makes this considerably more complicated, as the previous solution changes the Y value of an existing data point. Not to mention that there are no rows of data for many values of X / NewX, so the running sum table calculation will most likely not function as expected. If a running sum of Y is necessary, I would recommend exploring other options (like densification).