12 Replies Latest reply on Sep 1, 2016 8:51 AM by Sagar Agarwal

    How to smoothen the Line Graph?

    Sagar Agarwal

      Hello All,

       

      I am plotting multiple measures on the Rows shelf as shown below, and I need to "smoothen" the topmost line graph (i.e. the Close Mid graph)

      The Dates are aggregated by Month, so all other measures are also summed up to Months. However, since I want to plot the Price Data, I cannot sum them up. Instead, I want to show the prices as it is, be at any level of aggregation.

      I am attaching a snapshot from the Bloomberg terminal to give you an idea of what I want (See the top area graph in second snapshot).

       

       

        • 1. Re: How to smoothen the Line Graph?
          Simon Runc

          hi Sagar,

           

          So to 'smooth' a chart, you could use moving-average (one of the quick table-calcs), but from your description it doesn't sound like that's what you want. It sounds like you want the Close-Mid price as an aggregate (a weighted average of the costs price). One way to do this is to break it our into it's constituents and then re-create using an aggregation...

           

          So the total 'value' of a commodity, over any aggregation (be that over time or across multiple stocks) is [Close Mid Price] * [Traded Volume]. Let's call that [Total Value]...NB notice there is no aggregation wrapped around this formula, or any part of it, it is run at row level. From here we can then re-create the, Close Mid price as an aggregation, which would be

          SUM([Total Value])/SUM([Total Volume])

           

          Hopefully my terrible Excel drawing shows what I mean

           

           

          hope that helps, and makes sense.

          • 2. Re: How to smoothen the Line Graph?
            Sagar Agarwal

            Hi Simon,

             

            Apologies but now when I read your response, I realize I am not clear on what I want. I want to essentially plot the "Close Mid" price only, but I do not want the line graph to be jagged. Instead, I want it to be smooth (as shown in the Bloomberg terminal snapshot).

            In the Bloomberg terminal, the date axis is set at month level; but the prices are displayed smoothly between one tick mark to another tick mark.

            In my tableau snapshot, the prices plot all along one straight line for that tick mark, which is an issue for me.

             

            I hope I am clear now

             

            I have attached a workbook with similar sample data. Thanks.

            • 3. Re: How to smoothen the Line Graph?
              Simon Runc

              hi Sagar,

               

              So one way to do this, is just change the Price field to an aggregated measure...and show as Average. This will now take the average price per month and show that. However that isn't a weighted average, so could be inaccurate. As you don't have any other columns to weight I've shown this in the 'Use Average' tab. As I noticed, in your original screen-shot, you had traded volume...I've added this as a column to a separate data-source (just using random numbers), so you can see how this method works.

               

              Think of it this way....

               

              If you bought 1 bread-roll for £1 each and 9 bread-rolls for £5 each...the average price you paid for a bread roll isn't the average of £1 and £5 = £3...it's that you spent £46 (£1 x 1) + (£5 x 9), and for that you got 10 rolls. So you're average price per roll is £4.60

               

              Although moving average does 'smooth' the line, it is a Table Calculation, so is an aggregate of an aggregate, so can't be worked off from a dimension (non-aggregated)...and not (I don't think) what Bloomberg would show as that is not the accurate price for that month.

              • 4. Re: How to smoothen the Line Graph?
                Sagar Agarwal

                Hi Simon - just wanted to give you a heads-up - I will be trying to implement this today and I will share the outcome

                • 5. Re: How to smoothen the Line Graph?
                  Sagar Agarwal

                  Hi Simon,

                   

                  I am "Close" to what I want, but not really. Let me explain it to you.

                   

                  The calculation that you showed me was Volume Weighted Average Price (VWAP) - which gives me a close approximation of the price, but not the actual price. Below is the screenshot of what I got when i implemented this.

                  Here, I have calculated VWAP using the Close Mid prices for the Bond. Now, when I  hover over the data points, it shows me VWAP for the month (because the level is set to Months), or for the week (when I change the date level to weeks). However, what I want is that, irrespective of what date level I have set (months, or weeks, etc), I want the prices to be displayed exactly as it is (with minor tick marks).

                  I have created a small mockup in paint of what I am essentially looking for. In the mockup, the date level is set to weeks, so I want the prices to be shown for all days, with minor ticks in between. Even if I get this with VWAP price, I would be fine (will try to convince my boss ) but he essentially wants the actual price to be listed there. In Bloomberg, no matter what date levels I have set, the prices & the date axis will spread out eventually to show the correct prices.

                   

                  I hope this clears on my end goal.

                   

                  EDIT: Another reason why I want the exact prices is because I might be asked the question - okay, so the price moved from 10 to 20 in 1 week, but what was the highest / lowest price during this timeframe?

                   

                   

                  • 6. Re: How to smoothen the Line Graph?
                    Simon Runc

                    I see...so I actually think that this is just a 'level of aggregation' issue then.

                     

                    In the attached, I've used 'Exact Date' (rather than the Month aggregation), but changed the display formatting to only show an Axis Mark per Month...but all the days in-between are shown)...As you can see, where there is just one price per day, the VWAP and AP (used as a dimension) are the same. Where there is more than one price per day, the VWAP gives a single value, the AP shows all of them...an example of this is shown below

                     

                     

                    In the attached, I've also added a Moving Average (light gray line) showing a moving average over last 21 days...just as an idea for a smoothed line (I quite like this type of display, where you have the 'choppy' daily line, and a smoothed/trend line)

                    2 of 2 people found this helpful
                    • 7. Re: How to smoothen the Line Graph?
                      Sagar Agarwal

                      Hi Simon,

                       

                      Extremely grateful to you for this - many thanks - this is exactly what I was looking for

                      • 8. Re: How to smoothen the Line Graph?
                        Sagar Agarwal

                        Hi Simon - just one last thing - i had created a custom Date field - "Date Selector" which basically takes input from a parameter "Date Level Aggregator" (so that the user controls the date levels - weekly, monthly, quarterly, and so on).

                        When I use the raw "Price Date", the viz renders to what you have shown, but when I use this custom date field (with same settings - exact date & continuous) - the viz remains the earlier jagged look. Is the same possible when using this custom date field? Some screenshots below:

                         

                         

                        When using the custom Date Selector:

                         

                         

                         

                         

                        When using the actual data source field "Price Date":

                         

                        • 9. Re: How to smoothen the Line Graph?
                          Simon Runc

                          If you are using the field (as you are here) dis-aggregated...Tableau will plot all the (dis-aggregated) values, regardless of the level you specify (eg. Month)...so when you change the date-aggregation to Month, it will now plot every value for that month (which is why you get long vertical lines, which are basically from the MIN to MAX for that Month)....below shows what it's doing more clearly as I've changed the mark-type to circle

                           

                           

                           

                          It's only when you wrap it in an aggregated calculation, that Tableau can create a single mark for that level (eg. a single price for a month). Imagine if the price wasn't a number but a string, you'd expect Tableau to plot a mark for each string.

                           

                          As you want both daily, and the level the user selects...I'd suggest using my Dual-Axis version (where I had the moving average), and have the light grey line at the specified level (using the aggregated version), but always keeping the daily level on the other Axis.

                           

                          Hopefully that makes sense, but let me know if not.

                          1 of 1 people found this helpful
                          • 10. Re: How to smoothen the Line Graph?
                            Sagar Agarwal

                            I see - in this case, i'll rather give the Price chart a fixed axis on month and not allow any user level input. Because end of the day - no matter what level the user selects - the prices are going to remain per day basis.

                             

                            Thank you very much for your time and patience Simon, much appreciated

                            • 11. Re: How to smoothen the Line Graph?
                              Simon Runc

                              Cool...yes if you want an accurate price per day, then you will need to keep it at this level.

                               

                              You could allow both....see attached (also you might like the Select Date formula...notice that if I use the exact keyword in the parameter I only need a one line formula to change the level!...not only simpler, but if I want to add 'quarter' as an option, I just add it to the parameter list!!)

                              1 of 1 people found this helpful
                              • 12. Re: How to smoothen the Line Graph?
                                Sagar Agarwal

                                This is brilliant Simon, thank you so much - I have now started to imagine the possibilities that exist within Tableau. It's a long way to go though for me