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.