3 Replies Latest reply on May 10, 2018 7:11 AM by Zhouyi Zhang

    Calculating the amount per day on a chart

    Thomas Owen

      Hey community!


      I am a relative newbie to Tableau, and am trying to solve something that I think should be straightforward, but I just cannot figure out how to do it.


      I have a Tableau chart that shows on a timeline the exports of certain commodities.  This data is made up by summing all the individual movements of that commodity (on ships, mainly).


      So, in my underlying data I might have:


      Vessel          Departed          Load                         Load Location

      Vessel 01     01 Jan 2018     100 Barrels               Country 01

      Vessel 02     01 Jan 2018      150 Barrels               Country 02

      Vessel 03     02 Jan 2018     99 Barrels...etc          Country 0`


      So, this is currently represented like this on the output:



      Which is great!  Except....


      For crude, the general metric used isn't the total number of barrels produced, it's the barrels per day.  So, if a given country exported 100 barrels on day 1 on a ship, and 100 barrels on day 1 on a different ship then this would be 200 barrels per day.


      This is fine if I zoom into the above graph so, instead of it showing months, it shows days.  This would simply sum the exports per day - hence barrels per day.  The pill for they y-axis is simply SUM([Barrels Exported]). Or:



      But, if I zoom out to show weeks, months, quarters, or even years, then the thing falls down.  If I look at months, for example, then the chart sums all of the barrels exported in that month but I want it to give me the average amount of barrels exported per day in that month.


      So, to make the maths simple, let's look at a week.  Let's assume that there are 7 vessels departing in that week and each of them had 100 barrels on.  In my current graph, this would come up as 700 Barrels for that week - but the number I need is the barrels per day, which, in this case, would be 100 barrels per day for that week ((7 * 100) / 7).


      Of course, it gets even trickier when looking at months because, of course, the number of days in a month is not consistent.


      So, the dream is to still allow the users to zoom in and out of the timeline (change months to weeks...etc.) and not touch that functionality - but to be able to have the system generate the barrels per day regardless of the zoom level selected by the user on the x axis.


      Err.... help!