Tableau multiple charts with calculated field

0
votes

I have below raw data table

Table Name: A
Mode Country Month Visitor Air Switzerland Sep-16 Rob Air Switzerland Oct-16 Bob Air Switzerland Nov-16 Ben Air Rome Sep-16 Zen Air Rome Sep-16 Jen Air Rome Oct-16 Todd Air Rome Nov-16 Ron Air Italy Sep-16 John Air Italy Sep-16 Scott Air Italy Sep-16 Jeff Air Italy Oct-16 Audrey Air Italy Nov-16 Steve Ground Switzerland Sep-16 Rob Ground Switzerland Oct-16 Russel Ground Switzerland Oct-16 Zen Ground Rome Nov-16 Ron Ground Italy Sep-16 Dan Ground Italy Oct-16 Timmy Ground Italy Oct-16 Auidrey Water Switzerland Sep-16 Peter Water Switzerland Sep-16 Josh Water Switzerland Nov-16 Rob Water Rome Oct-16 Ben Water Rome Nov-16 Stuart Water Rome Nov-16 Jen Water Rome Nov-16 Todd Water Italy Sep-16 Scott Water Italy Sep-16 Jeff Water Italy Sep-16 LOLO Water Italy Sep-16 Dabbu Water Italy Nov-16 Danu Water Italy Oct-16 Randy Water Italy Oct-16 Demi

Table Name: B
Mode Month TotalVisitorCount Air Sep-16 2500 Ground Sep-16 30 Water Sep-16 100 Air Oct-16 1200 Ground Oct-16 10 Water Oct-16 3500 Air Nov-16 5000 Ground Nov-16 600 Water Nov-16 800

After inner joining these two tables on Mode and Month I want to achieve "Final Table" (below) for Nov-16.

  1. How do I have multiple measures/dimension on secondary vertical axis?
  2. How do I calculate "VisitorRate", because "DIV(countd[Visitors]/[Total Visitor Count])" formula says, I need aggregation on both sides.

Final Table
Mode/Country Switzerland Rome Italy VisitorRate Goal Air 3 4 5 (3+4+5)/5000 0.06 Ground 3 2 3 (3+2+3)/600 0.06 Water 3 4 7 (3+4+7)/800 0.06

So basically, I am trying to plot above "Final Table" from "TableA" and "TableB". Need below sample chart.

enter image description here

Any guidance would be appreciated.

Thanks

1
tableau-api
How the values in DesiredB table are calculated? I do not see any relation to Table Name A. – arturro
"DesiredTableB" is the sum(monthly visitor count) according to mode. The common fields between these two tables is "Month" and "Country". I want "sum of visitors" divide by "sum of monthly visitor count" – Tableau Beginner
I don get it . In DesiredB for Nov-16,Air and Rome you have value 20. How it is calculated? Table B, had value 2 for Nov-16,Air,Rome. Is there some formula or just the values are random ? – arturro
@arturro : These are random numbers. But what I am trying to get at is "Final Table" using above two raw data tables. "DesiredA" and "DesiredB" is my way of thinking to get to "Final Table". Sorry for the consuing post. I will update it. – Tableau Beginner
I don't see why we need table A? I have got it 80% there by plugging table B into tableau, pulling [Mode] into my columns shelf and SUM[Montlhy Visitor Count] into my Rows shelf and then pull countries into the the "color" marks card. This will give you the overall totals per mode. You can then pull the [Month] into the filter card and filter for whichever month you want.To use the overall total per mode you can use a calculated field and use a LOD function. Here it is, "{FIXED [Mode]:SUM([Monthly Visitor Count])}". That's as far as I got, hope this helps some what – lampbob

1 Answers

0
votes

I could get Visitor Rate = COUNTD(Visitor Name)/Sum(TotalVisitorCount)

For plotting multiple dimensions on secondary vertical axis, Just used Measure Values on the Row section.

How do I have multiple measures/dimension on secondary vertical axis? --Simple "A/B"

How do I calculate "VisitorRate", because "DIV(countd[Visitors]/[Total Visitor Count])" formula says, I need aggregation on both sides. -- Use Measure Values in row part.