your cart

Jet on map

    Suppose you work for a delivery compay and you need analyse order in a week. Let's take one address for example. Below are my data:   
OrderIDOrder DateCustomerIDCustomer NamePriceAddressLatitudeLongitude
D0016/23/2019C001Mike221050 Rue Drummond #102, Montréal, QC H3B 0G345.500491-73.578092
D0156/25/2019C001Mike311050 Rue Drummond #102, Montréal, QC H3B 0G345.500491-73.578092
D0056/24/2019C002Jerry291050 Rue Drummond #606, Montréal, QC H3B 0G345.500491-73.578092
D0066/27/2019C002Jerry481050 Rue Drummond #606, Montréal, QC H3B 0G345.500491-73.578092
D0166/23/2019C003Anna521050 Rue Drummond #269, Montréal, QC H3B 0G345.500491-73.578092
D0026/24/2019C003Anna511050 Rue Drummond #269, Montréal, QC H3B 0G345.500491-73.578092
D0036/25/2019C003Anna551050 Rue Drummond #269, Montréal, QC H3B 0G345.500491-73.578092
D0076/26/2019C003Anna591050 Rue Drummond #269, Montréal, QC H3B 0G345.500491-73.578092
D0086/27/2019C003Anna511050 Rue Drummond #269, Montréal, QC H3B 0G345.500491-73.578092
D0096/28/2019C003Anna601050 Rue Drummond #269, Montréal, QC H3B 0G345.500491-73.578092
D0106/29/2019C003Anna691050 Rue Drummond #269, Montréal, QC H3B 0G345.500491-73.578092
D0046/27/2019C004Larry901050 Rue Drummond #369, Montréal, QC H3B 0G345.500491-73.578092
D0116/24/2019C005William161050 Rue Drummond #1102, Montréal, QC H3B 0G345.500491-73.578092
D0126/25/2019C005William221050 Rue Drummond #1102, Montréal, QC H3B 0G345.500491-73.578092
D0136/27/2019C005William191050 Rue Drummond #1102, Montréal, QC H3B 0G345.500491-73.578092
D0146/29/2019C005William301050 Rue Drummond #1102, Montréal, QC H3B 0G345.500491-73.578092
    Then you drag Latitude to Rows and Longitude to Columns, Customer ID, Customer Name, Address, Order ID Order Date and Price to Detail. You will get a dot on map as below image:

    Since Tableau doesn't support Canada Detail such as street, full post code, we need use other mapservice.
    Check below link to know 'Use custom Mapbox styles in Tableau'
    https://docs.mapbox.com/help/troubleshooting/use-custom-mapbox-styles-in-tableau/
    After you have done, you zoom in the map and you can see strees and building. But however you zoom it, you still only see one dot, when you hover on this dot, you only see one order. That's because all orders are in same building, so they have same address and same geo coordinates.

    Then we will use 2 ways to jet the dots to show 16 orders.
    First way is random function, we need create 2 new coordinates:
    Random Jet Latitude: AVG([Latitude])+AVG((RANDOM()-0.5)/30000)
    Random Jet Longitude: AVG([Longitude])+AVG((0.5-RANDOM())/30000)
    RANDOM will return a random number between 0 and 1, devide by 30000 is to make them change a litte to existing coordinates, you can change it bigger to make dots closer or smaller to make dots  more scattered. You can notice that in 2 formula, one is AVG((RANDOM()-0.5)/30000), other is AVG((0.5-RANDOM())/30000), if you use same, the dot will be aligned.
    Then we duplicate original map just created and replace by using new calculated filed, after you zoom in you can see 16 dots:

    Second way is using index function, we need create another 2 new calculated fields:
    Index Jet Latitude: AVG([Latitude])+[Index]/100000
    Index Jet Longitude: AVG([Longitude])-[Index]/100000
    Then we duplicate original map just created and replace by using new calculated filed, after you zoom you still only see 1 dot. Why? Because Index function is a table calculation, it use 'Table(across)' by default. In this case, indext return 1, it's still same for each order.
    You need right click Index Jet Longitude and choose Edit Table Calculation, then choose Specific Dimenstions and choose all fields. Then you can see 16 dots align a line.
      
    Then we do the same for Index Jet Latitude and we will see:

    You can change 100000 bigger number to make dots closer, but we can make it centered like Random function by modifying table calculation.
    We want to see it range by customer, so we use Customer ID do it. For Index Jet Longitude we change At the level to "Customer ID", for Index Jet Latitudewe change Restarting every to "Customer ID". Or you can reverse it and get same effect.
                  
    At the level means we only compute to Customer ID. Each Customer ID will return a value, we have 5 customers, so it will return from 1 to 5. Restarting every means when we finish calculate first customer ID, we start from 1 again. After these setting, we seperate dots. Then you will see the dots arrage more reasonable. Each column is different customer.

    You can do more analysis by dragging price to color. From below image, we can see first customer has 4 orders, but price is low, increase his order amount is a consideration; Second custers has only 1 order, but price is very high, try to make him place more order will bring more profit. 3rd customer has many order and price is relatively high, keep him as loyal is a key.
    You can also use set action to let map show only 5 customers dots first, when click on one customer dot, other dot disappear, only orders from this customer shows.  I will update in next blog.


Blog Category