Facoltà di Economia Marco Biagi - Home



Lezioni Tableau per contenuto Getting started e grafici di baseStart screen - connect to excel “global superstore” drag orders - drag people - explain join integrated blend data sources - add “global…csv” explain join with blended dataField type - split - renameConnect live - extract--sheet 1Categ-segment / quantity-market - market to color--data pane, dimension discrete blue pills /measures numbers we want to analize, continuos green--sale seasonalityPut sales on rows, order date to see sales over time. Expand to quarter, pivot year with Quarters, year to color, click the quarter pills to change to monthsTable calculation -> Year over year growthBack sales into the vizMove Year over year to tooltipCategory on rows -> annotate point - dip in july, rebound in the fall--crosstabCopy image - copy data to excel - duplicate as crosstab - swap axis -fit entire -add profit to color - color edit - green gold 6 steps - marks to square - turn on mark label -category pill show highlighter--Map Global sales and profitShow me for sales and country - symbol map - add in state - size -transparency - border -hide legend -color profit - navigate india - unpin - duplicate sheet - let just country - group with lazo the southern emisphere -create group use group on sale seasonality to compare - keep only -category show filter-- Sales by Sub-Categorycategory- subcategory-product- hierarchies -sort - quick sort - profit on color -market on color - group items “small office supplies” - remove market - swap axis--Customer breakdownProfit, shipping cost - cat on color - customer name, profit on detail -disaggregate - trend line - R2 not meaningful - select customer with negative profit and drill through--Sales dashboardSize - sheets Map Global sales and profit - Sales by Sub-Category - Customer breakdown . hide titles . categories filters apply to worksheet - all using this data source - map use as filter--story pointsMap Global sales and profit overall sales are fine -but not always Sales dashboard with filter on texasGrafici di baseSales on rows - Subcat on rows, sales on columns, profit on colorCategory e segment on rows, market and quantity on column. Market on colorhighlight pivotWord cloud subcat, profit on colorSide by side bar sales, profit, costsBar inside a bar sales, costsPieDonutTreemapBubbleCalendar Lollipop stacked barMap Boxplot customer name sales + marketBullet chartFunnel 1 e 2Rank e indexMarket – countd customerMeasure names and valuesavg ref line –AVG fixedconnessione puntiJoin person (person, region, sales)Dashboard su public su grafici NON Getting started First story su public (vedi sol su public)Lezione Getting started primo, sales seasonality MappingMappare latitudini e longitudini. A?geographic information system?(or?GIS) is a system designed to capture, store, manipulate, analyze, manage, and present spatial or?geographical data.Coordinate Points Latitude indicates how far up or down from the equator, Longitude indicates how far east or west from the Prime Meridian. Geo fields must have a Geographic RoleAny point on a map can be represented with latitude and longitude coordinates. In Tableau, coordinates need to be numbers in decimal format. Positive latitudes indicate the northern hemisphere, positive longitudes indicate eastward from the Prime Meridian. In this way, every point on the globe has unique latitude and longitude coordinates. Incidentally, Tableau uses same projection as Google Maps, which is Web Mercator Plotting Latitude and Longitude If your dataset has latitude and longitude fields, Tableau can automatically plot them on a map. On the other hand, if your data doesn’t have latitude and longitude but you have geographic place names such as city, country, or province, Tableau will determine their coordinates for you provide the fields latitude (generated) and longitude (generated). Custom Geocoding If your data contains locations (without latitude and longitude coordinates) that Tableau cannot recognize, you can add to the database and enter your own custom geocoding, or simply blend in the geographic data. The videos Expanding Tableau’s Mapping Capabilities and Custom Geocoding go into more depth. 3 Polygon Maps Locations can be plotted on a map in two ways – as a point or mark to represent the entire area, or a polygon, covering the area. It’s also possible to provide your own polygon data to create custom polygon maps, such as this map of National Parks in the UK. Marks = polygonPointorder (point ID) -> pathPolygon ID -> detailDouble clic on lat and longOther Map Services - WMS serverIf the default map tiles aren’t what you need – maybe your analysis is of ocean currents – Tableau offers the option for connecting to Mapbox or a Web Map Service. GIS- tWMS service (usa solo open street) for the GEBCO global bathymetric grid Map Images Image hk metro map X left 0 right 840Y bottom 0 top 623Option always show imageUse annotation point to know the coordinatesX -> column shelfY -> rows shelfStation -> detailsLine->colorsMark->solid-> haloEserciziGlobal Superstore Orders 2016 - Automatically generated lat e long, geographic roleGIS prov reggio.xlsx, salesmanMap.twbx - plotting lat and longearthquake.xlsx, earthquake last year.twbx - plotting lat and long Polygon maps: polygon re.xlsx - polygon prov reggio.twbxBackground Images: hong_kong_mtr_coordinate_data.xlsx, hong_kong_mtr.png Aggiungi action URL o <station> excel like 14 objects (Customer ranking 3, Revenue analysis 3 (no speedometer), Product analysis 1, Geographic 3 (+ map), Salesman analysis 3Classifica clientiCustomer description to rowSum(fatturato 2012) to columnsCopy Sum(fatturato 2012) to rows - discrete -quick table calc-> rankCrate calculated field Index()Fatt 2012 to colorsCustomer description to filter -> top 10 by sum fat 2012, parameterMetti macro area, index su cust description, rank su tablesingle customer revenue analysismeasure names, values, drag out all fields but fat 2012. 2011, quant 2012, 2011crea delta F e delta QTrendDiscrete, years on colorsContinuiBullett graphAdd reference line constant 1Then edit, write 40 e 100%3810-254000then line 120% to create the targetProductGroups countwrong ranking groupif SUM([Fatturato2012])> 1000000 then"gold"elseif SUM([Fatturato2012]) <100000 then "bronze"else "silver"ENDRight groups using fixed syntax (FIXED e LOD can’t be used in some data source if you don’t use extract as a connection method es. From Ms access Extract and not live connection)if {fixed [CUSTOMER_DESCRIPTION]:SUM([Fatturato2012])>1000000} then "Gold"elseif {fixed [CUSTOMER_DESCRIPTION]:SUM([Fatturato2012])<100000} then "Bronze"ELSE "Silver"ENDCountdistinct (customer description) on textAreaTableSidebyside bars (to compare different macro area through the years):Stacked bar (remember to drag measure names on color)GeoSalesmanDrag invoice amount euroDate -> months (continuous)Salesman description to colorsavg salesman agenti attivi to rowdual axis clic axis syncronizeThe first element after the opening curly brace is one of the following scoping keywords:FIXEDFIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail—that is, without reference to any other dimensions in the view.FIXED level of detail expressions also ignore all the filters in the view other than context filters, data source filters, and extract filters.Example:?{ FIXED [Region] : SUM([Sales]) }INCLUDEINCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.INCLUDE level of detail expressions are most useful when including a dimension that isn’t in the view.Example:?{ INCLUDE?[Customer Name] : SUM([Sales]) }avg salesman agenti attivi{fixed DATETRUNC('month', [Data]):SUM([INVOICED_AMOUNT_EUR])}/{FIXED DATETRUNC('month', [Data]):COUNTD([SALESMAN_DESCRIPTION])}avg salesman tutti agenti{fixed DATETRUNC('month', [Data]):SUM([INVOICED_AMOUNT_EUR])}/{COUNTD([SALESMAN_DESCRIPTION])}Trascina il livello di data dallo scaffale al box editing della formula!!!Deve esserci almeno un salesman su tutti i mesi per vedere la media corretta!!!!From dashboard no code (Excel) 5 salesmanUsing SET top 5 (create a set for the first top 5 salesman and then use it on color)USING groupWith SETWith GroupSalesman and country sales 12Funnel 1Salesman on rowInvoiceamounteuro on rowsRepeat Invoiceamounteuro on rows with - so that - On Invoiceamounteuro label Salesman aligned leftOn -Invoiceamounteuro label InvoiceamounteuroHide header on y axisFunnel 2Invoiceamounteuro on rowsSalesman on colorInvoiceamounteuro on sizeSort decreasingWord cloudSalesman on text Invoicedamounteuro on sizeInvoicedamounteuro on colorMarks type textDonutsSelect group and ineru12 and create a pie chartAdd inveur12 and group as labelsDrag number of record twice on rows. On the second pill remove all the fields form marks pane and color white with transparency. Dual axis and synchronize axis. Add borders3810317500Starting from Staging complete:datagiustaDATE(RIGHT([INVOICE_DATE],2)+"/"+MID([INVOICE_DATE],5,2)+"/"+LEFT([INVOICE_DATE],4))inv2011-2012IF YEAR([datagiusta])=2011 THEN [INVOICED_AMOUNT_EUR] ELSE NULLENDGetting Started with Data Transcript Connect pane, we have a long list of native connections to all sorts of data sourcesConnecting to Multiple Tables (cross-database joins, integrated data source) to add columns from other tables in the same data source, we need to edit our data connection - To do so, click on the Data Source tab – add Global Superstore Excels.csv - Let’s join our Global Superstore Returns 2016s.csv to the orders table explain join. Join merge dataset at row level, needs a key in commondata blending aggregates at a query level. 2 data sources (es. Access coffee chain – excel office chain) – no primary key in common – just one common field separate queries to separate data sources and aggregation of results (es state, country) Live versus ExtractConnecting live leaves the data in the database or source file. - The other option is to extract the data into Tableau’s high performance in-memory data engineMetadata and Saving Data Sourceshierarchy - folders by right clicking and selecting Group by > folder - change the data type say, Row ID, change the data type to a string – field Default Properties , we can edit the default number format and the default aggregationIf we want to save this data source for further use, we right click up here on the data source and say “add to saved data sources”. This will save the connection as a .tds (note: this is not the data itself, just the information about the connection) and it’s saved in the local version of Tableau Desktop Managing ExtractsCreating Extracts - If we’re connected to a data source and decide that we’d like to extract it, we can do soby right clicking on the data source and selecting Extract Data. If the workbook has already been saved as a .twbx packaged workbook, the extract will automatically save as part of the packaged workbook. If the workbook has been saved as a .twb or hasn’t been saved yet, we’d be asked where to save the Tableau Data Extract file (.tde).using extract - Refreshing Extracts. Data prep:Saving and publishing data sources:Join types:Cross database joins:Data blending and additional data blending:Connecting to google analytics:Connecting to cubes:Visual analyticsGetting Started with Visual Analytics: Marks, changing, workkibg, aggregation and number of marks – highlighting – lod – show meDrill Down and Hierarchies: Hierarchy building, working, date (continuous date can’t be separate date part son can’t drill)Sorting: sort on axis – click the pill – use and drag the header in the legend – click a discrete pill and select sort option from menu – sort different pills different ways. Continuous pills are sorted using axis or toolbar – discrete dimensions pills full sorting option Grouping: use header (florida, small office supplies), use data pane “select field -> create -> group” es. Subcategories – edit group, include other, use marks “visual grouping”. Groups are metadata definitions, sets are combination of data like filters and can be used in calculationsAdditional Ways to Group (with or without parameter): calculation to define groups – if sum([profits])>[threshold] then “a” else “b” end. Using bins Creating Sets: Working with Sets:Ways to Filter: filter shelf, quick filter, in the view (keep/exclude) or legend, conditions (top, by field…) – remove filter by removing pillUsing the Filter Shelf:Quick (Interactive) Filters: layout mode, cascading filters, only relevant values, all valuesWhere Tableau Filters: summary level (normale, sulla dimensione della viz es category) – record level (normale, sui dati che costruiscono la dimensione della viz es profits) – data sourceAdditional Filtering Topics. Top, parameters, index(). Apply to worksheet, context filterParametersFormattingThe Formatting PaneTrend LinesReference LinesForecastingClusteringAnalysis with Cubes and MDXCalculations Getting Started with Calculations: regular calculation vs table calculationEsempi regular calculation (formulas):Costi: es. sum([sales])-Sum([profit])3451225-8064500Logic functions:Sign: IF sum([Profit])>0 then "Positive"else "Negative"ENDProfitcutoff:357568519685000if SUM([Profit])>150000 THEN "High"ELSE "Not high"END386143515938500Groupsbycalc:IF SUM([Sales])>100000 THEN "High"ELSEIF SUM([Sales])>10000 then "medium"ELSE "Poor"END38328601016000Fixed groups:if {fixed [Country]: SUM([Sales])>100000} then "Gold"elseif {fixed [Country]: SUM([Sales])>50000} then "Silver"else "Bronze"END3737610698500Parameter IF SUM([Sales])>[ParameterSales] THEN "High"ELSEIF SUM([Sales])>10000 then "medium"ELSE "Poor"381019367500ENDfixedSUB{FIXED [Sub-Category]: SUM([Sales])}includeSUB{include [Sub-Category]: SUM([Sales])}fixedCAT{fixed [Category]:SUM([Sales])}Fixedsales{fixed: SUM([Sales])} or {SUM([Sales])}Percentuale su totale riga (percentuale across) o totale colonna (down)2946400889000381019304000Percentuale su totale o subtotale colonna (percentuale down o panedown o subcat)Percentuale su totale generale (table)Rank e index computing on different part of the tableIntroduction to LOD Expressions:Intro to Table Calculations: secondary calculation performed on top of a returned result setModifying Table Calculations: TC can be written like any other calculation or pre-definedAggregate CalculationsDate CalculationsLogic CalculationsNumber CalculationsString CalculationsType CalculationsLod E LOD ExpressionsIl LOD di una viz viene deciso dalle dimensioni presenti sul grafico e si comporta come uno zoom. Più dimensioni inserisco (zoom in) più aumento la granularità disaggregando i dati. Meno dimensioni inserisco più aumenta il livello di aggregazione (zoom out). La stessa cosa posso farla usando le espressioni LOD: EXCLUDE, INCLUDE, FIXEDBoth the Include and Exclude calculation types are relative to the visualization in which they are used. If you Include a dimension that is already in the view, then the Include calculation will have no extra effect. Likewise, if you Exclude a dimension that is not in the view, then that calculation will have no effect.Exclude e include dipendono da cià ciò che c’è nel grafico. Fixed non dipende dalle dimensioni della vizEXCLUDEPermette di effettuare un calcolo escludendo una dimensione presente nel grafico es:INCLUDE fa il contrario: usa una dimensione non presente nella viz per restituire un calcolo diverso.Es. voglio calcolare la media di vendita per categoria in un grafico che presenta solo la media per regioneSpiegazione esercizio INCLUDE su mappa da video Tableau “Introduction to LOD Expressions”:se calcolo e riporto su mappa la media AVG dei profitti, ottengo la media di tutti i record relativi a quel paese. Es: argentina = -48. Se voglio calcolare il profitto medio per ordine (o per cliente, o qualsiasi altra dimensione) invece che per linea di ordine, devo specificare a Tableau che voglio calcolare la media rispetto a quella variabile. Per calcolare il valore medio degli ordini effettuati in Argentina, devo effettuare la somma di ogni riga d’ordine per ciascun ordine e poi farne la media per paese.Posso effettuare questo calcolo usando due diverse LOD expressions: INCLUDE o FIXEDLa sintassi è simile:{INCLUDE [Order ID]:SUM([Profit])}{FIXED [Order ID]:SUM([Profit])}Riportati sulla mappa, avrò 3 valori AVG profit = -48, sumincludeorder = -98, sumfixedorder =-98NOTA sull’espressione FIXEDFIXED consente di calcolare valori aggregati prima dell’introduzione di eventuali filtri o di ulteriori dimensioni nella viz. Se la dimensione fissa non comprare nella viz o viene filtrata una dimensione attiva, il numero di record e i risultati possono cambiareThe statement "FIXED calculations are applied before dimension filters, so unless you promote the fields on your Filter shelf to?Improve View Performance with Context Filters, they will be ignored." means the calculations are dependent only on the dimensions used in the LOD calculations.?But this doesn't mean the dimension filters do not affect the no.of dimensions in the view and thus affecting overall averages.Il risultato di {fixed: SUM([Sales])} or {SUM([Sales])}Equivale alla somma delle vendite che rimane fissa indipendentemente dalle variabili o dai filtri presenti nella viz. Come già mostrato, anche il calcolo FIXED della somma di vendite per categoria non risente della presenza di altre variabili o di filtri:{fixed [Category]:SUM([Sales])}Questo succede però solo se la dimensione usata per il raggruppamento (Category) compare nella viz. Se la dimensione di raggruppamento è diversa, i filtri influiscono sul calcolo. Ad esempio se uso {fixed [Order ID]:SUM([Sales])}E lo confronto con {fixed [Category]:SUM([Sales])} si vede che la prima è sensibile sia alle dimensioni (segment) che ai filtri EXCLUDEINCLUDEFIXED RIPETE LE DUE AZIONI PRECEDENTIConta.se{FIXED : COUNTD(if [Market]="Africa" then ([Customer Name])END)}OPPURE COUNTD(if [Market]="Africa" then ([Customer Name])END)Somma.se{FIXED : SUM(if [Market]="Africa" then ([Sales])END)}OPPURE SUM((if [Market]="Africa" then ([Sales])END))Dashboards and StoriesGetting Started with Dashboards and Stories Building a DashboardDashboard Layouts and FormattingDevice Designer Dashboard Interactivity using ActionsStory Points onlineTableau Online course site:: imbibordoni@tiscali.itPassword: corsoDCI18 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download