Tableau Prep-hjälp

1. För att svara på denna fråga i Tableau Desktop använder vi DATEDIFF-funktionen. Den här funktionen tar upp tre argument – datumdel, startdatum och slutdatum. Eftersom vi vill få reda på antalet dagar mellan olika händelser använder vi datumdelen ”dag”. Våra start- och slutdatum finns i datauppsättningen som 1:a överträdelsedatumet och 2:a överträdelsedatumet.

2. Beräkningen är:

   Tid mellan överträdelser = DATEDIFF('day', [1st Infraction Date], [2nd Infraction Date])

3. Vi kan plotta denna beräkning mot Förar-ID som ett stapeldiagram. Var uppmärksam på att sju förare inte hade någon andra överträdelse och därför visas sju nollor.

   

1. För att svara på den här frågan i Tableau Desktop skapar vi ett punktdiagram över 1:a bötesbeloppet och 2:a bötesbeloppet. Genom att lägga Förar-ID i hyllan Detalj på kortet Markeringar kan vi skapa en markering för varje förare.

2. Om du vill lägga till en trendlinje använder du fliken Analys i den vänstra rutan och tar fram en linjär trendlinje. Genom att hovra över trendlinjen kan vi se att R-kvadratvärdet praktiskt taget är noll, medan p-värdet är långt över brytpunkten för betydelse. Vi kan konstatera att det inte finns någon korrelation mellan det första och det andra bötesbeloppet.

   Om vi använder detta punktdiagram i en instrumentpanel måste trendlinjen tas bort.

När vi vill göra en djupare analys kan vi behöva skapa vissa beräkningar.

1. För att svara på detta i Tableau Desktop måste vi lägga till böterna för båda överträdelserna i ett enskilt fält. Eftersom vissa förare kanske inte har haft någon andra överträdelse måste vi använda funktionen noll-null ZN, för att omvandla eventuella null-värden för 2:a bötesbeloppet till nollor. Underlåtenhet att göra detta kommer att leda till null-värden, om det inte finns någon andra böter.

2. Beräkningen är:

   Totalt betalt belopp = [1st Fine Amount] + ZN([2nd Fine Amount])

3. Vi kan plotta Totalt betalt belopp mot Förar-ID och sortera stapeldiagrammet.

1. För att svara på detta i Tableau Desktop måste vi göra en mer komplicerad IF beräkning, som jämför om den första och andra överträdelsetypen är desamma. Om de är det bör man tilldela värdet ”1”. Om de inte är desamma bör man tilldela ”2”. Eftersom vi bara bryr oss om flera olika överträdelsetyper tilldelas alla andra resultat ”1”, såsom en andra överträdelsetyp med null-värde.

2. Beräkningen är:

   Antal typer av överträdelser =

   IF [1st Infraction Type]=[2nd Infraction Type] THEN 1 
   ELSEIF [1st Infraction Type]!= [2nd Infraction Type] THEN 2 
   ELSE 1 END

3. Vi kan sedan plotta Antal typer av överträdelser mot Förar-ID och sortera stapeldiagrammet.

1. Om vi vill besvara detta i Tableau Desktop räcker det inte med att bara dela det totala bötesbeloppet med två, eftersom vissa förare bara hade en överträdelse. Vi kan inte heller beräkna genomsnittligt bötesbelopp per förare och ta genomsnittet av dessa värden, eftersom beräkning av genomsnitt för medelvärden kan leda till inkonsekvenser. Vi måste i stället beräkna det totala belopp som betalats av förare som aldrig gått i körskola, och sedan dividera med det totala antalet överträdelser i samband med dessa böter.

   1. Först måste vi fastställa om varje förare hade en andra överträdelse. Vi kan utnyttja det faktum att informationen i alla ”2:a”-fälten är null om det inte fanns någon andra överträdelse och ställa upp beräkningen:

      IFNULL([2nd Infraction Type], 'no')

      Detta returnerar en överträdelsetyp, om en sådan finns, eller ”ingen” om det inte finns någon andra överträdelse.

   2. Sedan måste vi omvandla denna information till antal överträdelser – 1 eller 2. Om resultatet av vår beräkning IFNULL är ”ingen”, så ska föraren markeras som att ha bötfällts en gång. Alla andra resultat bör markeras med två böter. Beräkningen är:

      Antal överträdelser =

      IF IFNULL([2nd Infraction Type], 'no') = 'no' THEN 1 
      ELSE 2 
      END

   3. Nu måste vi överväga det totala bötesbeloppet. Precis som för fråga 3 ovan måste vi lägga till det första och andra bötesbeloppet med en ZN-funktion runt det andra. Eftersom vi vill att beloppet ska beräknas för hela datauppsättningen är det bästa praxis att ange aggregeringarna, SUMMAN, i själva beräkningen. Beräkningen är: 

      SUM([1st Fine Amount]) + SUM( ZN([2nd Fine Amount]) )

   4. Nu tar vi det totala bötesbeloppet och delar det med det nya värdet för det beräknade fältet Antal överträdelser för att fastställa det genomsnittliga bötesbeloppet:

      Genomsnittligt bötesbelopp = ( SUM([1st Fine Amount]) + SUM( ZN([2nd Fine Amount]) ) ) / SUM([Number of Infractions])

2. Vi måste också filtrera bort förare som har gått i körskola, men den informationen lagras också i två fält.

   1. Tableau fungerar väldigt effektivt vid numeriska beräkningar. Vi formulerar detta med siffror, för att öka prestandan så mycket som möjligt. För att kombinera dessa två fält skapar vi en beräkning för varje fält som säger ”Ja=1” och ”Nej=0” (null-värdet bör också vara 0, för förare utan en andra överträdelse). Genom att summera resultatet av dessa beräkningar har förare med ett totalt värde på 0 aldrig gått i körskola (och ett värde på 1 eller 2 visar hur många gånger de har gått där). Vi kan därefter filtrera så att endast förare med värdet 0 behålls.

   2. Den här gången använder vi en CASE sats istället för IF. De fungerar på liknande sätt men har olika syntax. Början av beräkningen ska se ut så här:

      CASE [1st Traffic School]
      WHEN 'Yes' THEN 1
      WHEN 'No' THEN 
      ELSE 0
      END

   3. Därefter gör vi samma sak för den 2:a körskolan. Vi kan lägga till båda delarna i samma beräkning genom att sätta varje ärendesats inom parentes och lägga till ett plustecken mellan dem. Om du tar bort några av radbrytningarna ser det ut så här:

      Antal körskolelektioner =

      (CASE [1st Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)
      +
      (CASE [2nd Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)
      

   4. Om vi drar Antal körskolelektioner till Dimensionsområdet i rutan Data (ovanför linjen) blir värdena 0–2 unika.

   5. Om vi nu filtrerar efter Antal körskolelektioner kan vi välja enbart 0 och vet då att vi hittar de förare som aldrig gått i körskola.

3. Om vi vill besvara den ursprungliga frågan är allt vi behöver göra att ta Genomsnittligt bötesbelopp till hyllan Text på kortet Markeringar.

   Eftersom vi byggt in aggregeringarna i beräkningen blir aggregeringen för fältet AGG, vilket inte går att ändra. Detta är som förväntat.

1. Precis som vi gjorde med den första datauppsättningen använder vi DATEDIFF ffunktionen för att svara på detta i Tableau Desktop. Den här funktionen kräver ett startdatum och ett slutdatum. Den här informationen finns i våra data, men allt finns samlat i ett enda fält. Vi måste sprida det över två fält.

   1. Skapa två preliminära beräknade fält:

      1:a överträdelsedatumet = IF [Infraction Number] = "1st" THEN [Infraction Date] END

      2:a överträdelsedatumet = IF [Infraction Number] = "2nd" THEN [Infraction Date] END

   2. Eftersom vi vill säkerställa att båda dessa värden är tillgängliga för jämförelse för varje förare, måste vi fästa dem på nivån för Förar-ID.

      Obs! Tror du mig inte? Försök att göra en DATEDIFF beräkning med dessa två fält som de är: Tid mellan överträdelser = DATEDIFF('day', [1st Infraction Date], [2nd Infraction Date])
      Du får null-resultat överallt, eftersom Tableau försöker jämföra med en datastruktur som ser ut så här:
      
      Här vet raden som vet vad det första datumet är inte vad det andra datumet är och vice versa. För att komma runt detta använder vi ett FIXED LOD-uttryck (detaljnivå) för att tvinga dessa första och andra datum att vara kopplade till Förar-ID.

   Redigera varje beräkning enligt följande:

   1:a överträdelsedatumet = { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "1st" THEN [Infraction Date] END ) }

   2:a överträdelsedatumet = { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "2nd" THEN [Infraction Date] END ) }

   Obs! Den ursprungliga IF-beräkningen måste aggregeras när den är inbäddad i ett LOD-uttryck (detaljnivå). Vi kan använda vilken grundläggande aggregering som helst som bevarar datumvärdet (så aggregeringar såsom SUM, AVG eller MIN fungerar, men inte CNT eller CNTD).

   Obs! Dessa beräkningar kan också skapas i Tableau Prep Builder. Mer information om LOD-uttryck i Prep finns i Skapa beräkningar för detaljnivå, rankning och sektioner.

   3. Nu kan vi skapa DATEDIFF beräkningen enligt följande:

      Tid mellan överträdelser = DATEDIFF('day', [1st Infraction Date], [2nd Infraction Date])

      • Om vi vill titta på veckor eller månader räcker det att ändra datumdelen (för närvarande 'day').

      • Det skulle också vara möjligt att skapa en enda beräkning för alltihop, genom att placera FIXED-beräkningarna direkt i DATEDIFF: 

        DATEDIFF ( 'day', 
        { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "1st" THEN [Infraction Date] END ) },
        { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "2nd" THEN [Infraction Date] END ) }
        )

   4. Plotta Tid mellan överträdelser på kolumner och Förar-ID på rader.

Resultaten kommer att vara identiska med utfallet för den ej pivoterade datastrukturen.

1. För att svara på detta i Tableau Desktop använder vi en logik som liknar den som använts för föregående fråga. Vi använder Överträdelsenummer för att identifiera om en given rad är den första eller andra överträdelsen, och tar sedan fram bötesbeloppet utifrån detta.

   1. Om vi bara vill göra ett punktdiagram kan vi hoppa över LOD-delen (detaljnivå) och bara använda IF beräkningen:

      1:a bötesbeloppet = IF [Infraction Number] = "1st" THEN [Fine Amount] END

      2:a bötesbeloppet = IF [Infraction Number] = "2nd" THEN [Fine Amount] END

   2. Om vi å andra sidan vill jämföra och se skillnaden i belopp mellan de första och andra böterna för en och samma förare, stöter vi på samma problem med null-värden som vi gjorde med datumen. Det kan inte skada att innefatta dessa beräkningar i en FIXED LOD, så det kan vara bra att göra det redan från början:

      1:a bötesbeloppet = { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "1st" THEN [Fine Amount] END ) }

      2:a bötesbeloppet = { FIXED [Driver ID] : MIN ( IF [Infraction Number] = "2nd" THEN [Fine Amount] END ) }

      Dessa beräkningar kan också skapas i Tableau Prep Builder. Mer information om LOD-uttryck i Prep finns i Skapa beräkningar för detaljnivå, rankning och sektioner.

   3. Skapa ett punktdiagram med 1:a bötesbeloppet på kolumner och 2:a bötesbeloppet på rader, och få fram en linjär trendlinje precis som tidigare.

Resultaten kommer att vara identiska med utfallet för den ej pivoterade datastrukturen.

1. Om man vill få denna fråga besvarad i Tableau Desktop är den pivoterade datastrukturen idealisk. Allt vi behöver göra är att ta fram Förar-ID och Bötesbelopp i ett stapeldiagram. Standardaggregeringen är redan SUMMA, så det totala belopp som betalats av föraren plottas automatiskt.

Resultaten kommer att vara identiska med utfallet för den ej pivoterade datastrukturen.

1. Om man vill få denna fråga besvarad i Tableau Desktop är den pivoterade datastrukturen idealisk. Allt vi behöver göra är att ta fram Förar-ID och ett Unikt antal Överträdelsetyper som ett stapeldiagram, för att få vårt svar.

Resultaten kommer att vara identiska med utfallet för den ej pivoterade datastrukturen.

1. Om vi vill besvara detta i Tableau Desktop räcker det inte med att bara dela det totala bötesbeloppet med två, eftersom vissa förare bara hade en överträdelse. Vi kan inte heller beräkna genomsnittligt bötesbelopp per förare och ta genomsnittet av dessa värden, eftersom beräkning av genomsnitt för medelvärden kan leda till inkonsekvenser. Vi måste i stället beräkna det totala belopp som betalats av förare som aldrig gått i körskola, och sedan dividera med det totala antalet överträdelser i samband med dessa böter.

   1. Först måste vi fastställa om varje förare hade en andra överträdelse. Vi kan utnyttja det faktum att 2:a överträdelsedatumet är null om det inte fanns någon andra överträdelse och ställa upp beräkningen:

      IFNULL(STR([2nd Infraction Date]), 'no')

      Detta returnerar datumet för den andra överträdelsen, i förekommande fall, eller ”ingen” om det inte finns någon andra överträdelse.

      Obs! STR-delen av denna beräkning är nödvändig eftersom IFNULL behöver en konsekvent datatyp i dess argument. Eftersom vi vill returnera strängen ”inga” för null-värden måste vi även konvertera datumet till en sträng.

   2. Sedan måste vi omvandla denna information till antal överträdelser – 1 eller 2. Om resultatet av vår beräkning IFNULL är ”ingen”, så ska föraren markeras som att ha bötfällts en gång. Alla andra resultat bör markeras med två böter. Beräkningen är:

      Antal överträdelser =

      IF IFNULL(STR([2nd Infraction Date]), 'no')= 'no' THEN 1 
      ELSE 2 
      END
      

   3. Nu måste vi överväga det genomsnittliga bötesbeloppet. Vi har redan ett enskilt fält för Bötesbelopp. Allt vi behöver göra är att dividera det med vårt nya fält Antal överträdelser och sätta in båda i SUMMA: 

      Genomsnittligt bötesbelopp = (SUM([Fine Amount]) / SUM([Number of Infractions])

2. Vi måste även filtrera bort de förare som har gått i körskola. Det ser ut som vi skulle kunna använda fältet Trafikskola och filtrera på Trafikskola = nej. Detta skulle dock filtrera på överträdelser som inte är kopplade till trafikskola, inte förare som aldrig gick i trafikskola. Om en förare har gått i körskola för en överträdelse, men inte för den andra, vill vi inte att någon av dessa överträdelse ska övervägas här. Den här föraren har gått i körskola och passar därför inte in på parametrarna för frågan.

   Vi behöver därför filtrera bort de förare som har gått i körskola. När det gäller data vill vi filtrera bort alla förare som har ett ”Ja” för Körskola på någon rad, oavsett vilken överträdelse svaret är förknippat med. Vi ställer upp beräkningen i etapper med hjälp av en enkel vy så att vi kan följa vad som händer:

   1. Först vill vi veta om en förare har ett ”Ja” för körskola. Dra Förar-ID till Rader och Körskola till Kolumner. Vi får en texttabell med platshållartexten ”Abc”, som anger relevanta värden för varje förare.

   2. Därefter vill vi ställa upp en beräkning som identifierar om värdet för Körskola är ”Ja”. Den första etappen i beräkningen är:

      Gått i körskola = CONTAINS([Traffic School), 'Yes')

      Om vi tar med oss Gått i körskola till hyllan Färg på kortet Markeringar , ser vi att alla etiketteringar i kolumnen ”Nej” har märkts som ”Falskt”, medan alla markeringar i kolumnen ”Ja” har märkts som ”Sant”.

      

   3. Men vad vi verkligen vill ha är denna information vad gäller föraren, inte överträdelsen. Ett LOD-uttryck är ett naturligt val när man försöker beräkna ett resultat på en annan detaljnivå än den grundläggande datastrukturen. Vi gör det här till ett FIXED LOD-uttryck. Hur som helst måste den aggregerade uttrycksdelen av ett LOD (detaljnivå) aggregeras. Tidigare har vi använt MIN. Fungerar det i det här fallet? Vi justerar beräkningen enligt följande: 

      Gått i körskola = { FIXED [Driver ID] : MIN( CONTAINS([Traffic School], 'Yes'))}

      

      När vyn har ändrats på detta sätt ser vi motsatsen till vad vi vill. Alla förare som har ett ”Nej” markeras som ”Falskt” överallt. Vi vill å andra sidan föra över ett ”Ja” som ”Sant” för alla poster för den föraren. Vad gör MIN här? Den väljer det första svaret i alfabetisk ordning, det vill säga ”Nej”.

   4. Tänk om vi ändrade det till MAX? Skulle det ta det sista svaret i alfabetisk ordning? Vi justerar beräkningen enligt följande: 

      Gått i körskola = { FIXED [Driver ID] : MAX ( CONTAINS( [Traffic School], 'Yes') ) }

      

      Och här har vi det: om en förare har ”Ja” någonstans i sina data, markeras det som ”Sant” att de har gått i körskola, även för den överträdelse som inte involverade körskolan.

   5. Om vi tar med oss Gått i körskola till hyllan Filter och väljer endast ”Falskt”, kommer vi bara att se förare som aldrig har gått på körskola.

3. Om vi vill besvara den ursprungliga frågan, med filtret på plats, behöver vi bara ta Genomsnittligt bötesbelopp till hyllan Text på kortet Markeringar. Eftersom vi byggt in aggregeringarna i beräkningen blir aggregeringen för fältet AGG, vilket inte går att ändra. Detta är som förväntat.

Resultaten kommer att vara identiska med utfallet för den ej pivoterade datastrukturen.

1. För att svara på detta i Tableau Desktop använder vi funktionen DATEDIFF . Den här funktionen kräver ett startdatum och ett slutdatum. Den här informationen finns i våra data, men allt finns samlat i ett enda fält. Vi måste sprida det över två fält. Eftersom vi vill säkerställa att båda dessa värden är tillgängliga för jämförelse för varje förare, måste vi fästa dem på nivån för Förar-ID.

   1. För att hitta det första överträdelsedatumet använder vi beräkningen:

      1:a överträdelsen = { FIXED [Driver ID] : MIN ( [Infraction Date] ) } 

   2. Vi gör det andra överträdelsedatumet i etapper.

      1. Till att börja med behöver vi bara titta på de datum som är senare än det första datumet:

         IF [Infraction Date] > [1st Infraction] THEN [Infraction Date] END

      2. Men detta ger oss varje överträdelse efter den första, och vi vill bara se den andra. Därför vill vi hitta det tidigaste datumet. Sätt in alltsammans i MIN:

         MIN( IF [Infraction] : [1st Infraction] THEN [Infraction Date] END )

      3. Vi vill också omberäkna det andra överträdelsedatumet för varje förare. Det är där LOD-uttrycken kommer in i bilden. Vi åtgärdar detta till nivån för Förar-ID:

         2:a överträdelsen = { FIXED [Driver ID] : MIN ( IF [Infraction Date] > [1st Infraction] THEN [Infraction Date] END ) }

   3. Och vi kan nu skapa DATEDIFF beräkningen:

      Tid mellan överträdelser = DATEDIFF('day', [1st Infraction], [2nd Infraction])

Resultaten kommer att vara identiska med utfallet för de andra två datastrukturerna.

1. För att svara på detta i Tableau Desktop använder vi en logik som liknar den som använts för versionen med pivoterade data för den här frågan. Vi använder fälten 1:a överträdelsen och 2:a överträdelsen som skapats för fråga I för att identifiera om en given rad är den första eller andra överträdelsen, och tar sedan fram bötesbeloppet utifrån detta.

   1. Om vi bara vill göra ett punktdiagram kan vi hoppa över LOD-delen (detaljnivå) och bara använda IF beräkningen:

      1:a bötesbeloppet = IF [1st Infraction] = [Infraction Date] THEN [Fine Amount] END

      2:a bötesbeloppet = IF [2nd Infraction] = [Infraction Date] THEN [Fine Amount] END

   2. Om vi å andra sidan vill jämföra och se skillnaden i belopp mellan de första och andra böterna för en och samma förare, stöter vi på samma problem med null-värden som vi gjorde i den första datastrukturen. Det kan inte skada att sätta in dessa beräkningar i en FIXED LOD, så det kan vara bra att göra det redan från början:

      1:a bötesbeloppet = { FIXED [Driver ID] : MIN ( IF [1st Infraction] = [Infraction Date] THEN [Fine Amount] END ) }

      2:a bötesbeloppet = {FIXED [Driver ID] : MIN( IF [2ndInfraction] = [Infraction Date] THEN [Fine Amount] END ) }

Resultaten kommer att vara identiska med utfallet för de andra två datastrukturerna.

1. För att svara på detta i Tableau Desktop måste vi först förstå något om LOD-metoden. Båda metoderna använder Tableau Prep för att filtrera bort poster som inte är den första eller andra överträdelsen för en förare. LOD-metoden i Tableau Desktop sparar alla poster. Detta innebär att om vi ska skapa en visualisering av SUMMAN (betalt belopp) av Förar-ID, visar Tableau Desktop-versionen högre belopp för förare med mer än två överträdelser. För att få värdet Totalt betalt belopp från de fullständiga data som matchar de andra metoderna, måste vi istället summera de första och andra böterna som vi gjorde med den första datastrukturen, istället för att använda det ursprungliga fältet Bötesbelopp.

2. Med hjälp av de fält vi skapade för fråga 2 lägger vi till de två bötesbeloppen.ZN krävs för att förhindra ett null-resultat för alla förare som bara hade en överträdelse. Beräkningen är: 

   Totalt betalt belopp = [1st Fine Amount] + ZN([2nd Fine Amount])

Resultaten kommer att vara identiska med utfallet för de andra två datastrukturerna.

1. För att svara på den här frågan i Tableau Desktop kan vi inte bara ta fram Förar-ID och ett Unikt antal Överträdelsetyper. Eftersom denna datauppsättning har fler än två överträdelser, kan vissa förare ha mer än två överträdelsetyper. För att matcha resultaten med de andra metoderna måste vi begränsa omfattningen till de två första överträdelserna.

2. Vi kan ta fram 1:a och 2:a överträdelsetypen, sätta in dem i LOD-uttrycken för att göra dem FIXED för föraren, och sedan använda en IF beräkning för att räkna typerna:

   1. 1:a överträdelsetypen = { FIXED [Driver ID] : MIN ( IF [1st Infraction] = [Infraction Date] THEN [Infraction Type] END ) }

   2. 2:a överträdelsetypen = { FIXED [Driver ID] : MIN ( IF [2nd Infraction] = [Infraction Date] THEN [Infraction Type] END ) }

   3. Antal typer av överträdelser =

      IF [1st Infraction Type] = [2nd Infraction Type] THEN 1 
      ELSEIF [1st Infraction Type] != [2nd Infraction Type] THEN 2
      ELSE 1 END 

      Obs! Det går också att skapa många av dessa beräkningar som ett enskilt fält genom att kapsla in de inledande beräkningarna direkt i den större beräkningen. Här skulle den kombinerade beräkningen se ut så här:
      IF
{FIXED [Driver ID] : MIN(IF [1st Infraction]=[Infraction Date] THEN [Infraction Type] END)}
=
{FIXED [Driver ID] : MIN(IF [2nd Infraction]=[Infraction Date] THEN [Infraction Type] END)}
THEN 1

ELSEIF
{FIXED [Driver ID] : MIN(IF [1st Infraction]=[Infraction Date] THEN [Infraction Type] END)}
!=
{FIXED [Driver ID] : MIN(IF [2nd Infraction]=[Infraction Date] THEN [Infraction Type] END)}
THEN 2

ELSE 1
END
      
      Vilket är lite svårare att förstå, men fungerar vid behov. (Observera att radbrytningar och vissa blanksteg inte påverkar hur beräkningen tolkas av Tableau.)

3. Vi kan sedan plotta Antal typer av överträdelser mot Förar-ID och sortera stapeldiagrammet.

Resultaten kommer att vara identiska med utfallet för de andra två datastrukturerna.

1. Om vi vill besvara detta i Tableau Desktop räcker det inte med att bara dela det totala bötesbeloppet med två, eftersom vissa förare bara hade en överträdelse. Vi kan inte heller beräkna genomsnittligt bötesbelopp per förare och ta genomsnittet av dessa värden, eftersom beräkning av genomsnitt för medelvärden kan leda till inkonsekvenser. Vi måste i stället beräkna det totala belopp som betalats av förare som aldrig gått i körskola, och sedan dividera med det totala antalet överträdelser i samband med dessa böter.

   1. Först måste vi fastställa om varje förare hade en andra överträdelse. Vi kan utnyttja det faktum att informationen i alla ”2:a”-fälten är null om det inte fanns någon andra överträdelse och ställa upp beräkningen:

      IFNULL([2nd Infraction Type], 'no')

      Detta returnerar en överträdelsetyp, om en sådan finns, eller ”ingen” om det inte finns någon andra överträdelse.

   2. Sedan måste vi omvandla denna information till antal överträdelser – 1 eller 2. Om resultatet av vår beräkning IFNULL är ”ingen”, så ska föraren markeras som att ha bötfällts en gång. Alla andra resultat bör markeras med två böter. Beräkningen är:

      Antal överträdelser =

      IF IFNULL([2nd Infraction Type], 'no') = 'no' THEN 1
      ELSE 2
      END

   3. För totalt betalt belopp kan vi använda beräkningen från fråga 3. Nu tar vi det totala bötesbeloppet och delar det med det nya värdet för det beräknade fältet Antal överträdelser för att fastställa det genomsnittliga bötesbeloppet:

      Genomsnittligt bötesbelopp = SUM([Total Amount Paid]) / SUM([Number of Infractions])

2. Vi måste även filtrera bort de förare som har gått i körskola. Vi kan inte använda samma metod som för den pivoterade datastrukturen, eftersom den här datauppsättningen innehåller förare med en tredje eller fjärde överträdelse. Istället följer vi samma metod som för de data som inte är pivoterade, och som sammanfattas här:

   1. Först måste vi ställa upp två beräkningar som identifierar om den första och andra överträdelsen involverade körskolan eller inte: 

      1:a körskolan = { FIXED [Driver ID] : MIN (IF [1st Infraction] = [Infraction Date] THEN [Traffic School] END ) }

      2:a körskolan = { FIXED [Driver ID] : MIN (IF [2nd Infraction] = [Infraction Date] THEN [Traffic School] END ) }

   2. Sedan lägger vi till dessa värden för att få det totala antalet körskolelektioner: 

      Antal körskolelektioner =

      (CASE [1st Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)
      +
      (CASE [2nd Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)

   3. Om vi drar Antal körskolelektioner till Dimensionsområdet i rutan Data blir värdena 0–2 unika.

   4. Om vi nu filtrerar efter Antal körskolelektioner kan vi välja enbart 0 och vet då att vi hittar de förare som aldrig gått i körskola.

3. Om vi vill besvara den ursprungliga frågan är allt vi behöver göra att ta Genomsnittligt bötesbelopp till hyllan Text på kortet Markeringar. Eftersom vi byggt in aggregeringarna i beräkningen blir aggregeringen för fältet AGG, vilket inte går att ändra. Detta är som förväntat.

Resultaten kommer att vara identiska med utfallet för de andra två datastrukturerna.

Föraröverträdelser

• Tid mellan överträdelser = DATEDIFF('day', [1st Infraction Date], [2nd Infraction Date])

• Totalt betalt belopp =[1st Fine Amount] + ZN([2nd Fine Amount])

• Antal typer av överträdelser = IF [1st Infraction Type]=[2nd Infraction Type] THEN 1 ELSEIF [1st Infraction Type]!= [2nd Infraction Type] THEN 2 ELSE 1 END

• Antal överträdelser = IF IFNULL([2nd Infraction Type], 'no') = 'no' THEN 1 ELSE 2 END

• Genomsnittligt bötesbelopp = ( SUM([1st Fine Amount]) + SUM( ZN([2nd Fine Amount]) ) ) / SUM([Number of Infractions])

• Antal körskolelektioner = (CASE [1st Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) + (CASE [2nd Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)

Pivoterade föraröverträdelser

• 1:a överträdelsen = {FIXED [Driver ID] : MIN(IF [Infraction Number] = "1st" THEN [Infraction Date] END)}

• 2:a överträdelsen = {FIXED [Driver ID] : MIN(IF [Infraction Number] = "2nd" THEN [Infraction Date] END)}

• Tid mellan överträdelser = DATEDIFF('day', [1st Infraction], [2nd Infraction])

• 1:a bötesbeloppet = {FIXED [Driver ID] : MIN( IF [Infraction Number] = "1st" THEN [Fine Amount] END ) }

• Antal överträdelser = IF IFNULL(STR([2nd Infraction]), 'no')= 'no' THEN 1 ELSE 2 END

• Genomsnittligt bötesbelopp = SUM([Fine Amount])/SUM([Number of Infractions])

• Gått i körskola = { FIXED [Driver ID] : MAX( CONTAINS([Traffic School], 'Yes'))}

LOD (detaljnivå) föraröverträdelser

• 1:a överträdelsen = {FIXED [Driver ID] : MIN([Infraction Date])}

• 2:a överträdelsen = { FIXED [Driver ID] : MIN( IF [Infraction Date] > [1st Infraction] THEN [Infraction Date] END ) }

• Tid mellan överträdelser = DATEDIFF('day', [1st Infraction], [2nd Infraction])

• 1:a bötesbeloppet = {FIXED [Driver ID] : MIN( IF [1st Infraction] = [Infraction Date] THEN [Fine Amount] END ) }

• 2:a bötesbeloppet = {FIXED [Driver ID] : MIN( IF [2nd Infraction] = [Infraction Date] THEN [Fine Amount] END ) }

• Totalt betalt belopp = [1st Fine Amount] + ZN([2nd Fine Amount])

• 1:a överträdelsetypen = {FIXED [Driver ID] : MIN( IF [1st Infraction] = [Infraction Date] THEN [Infraction Type] END ) }

• 2:a överträdelsetypen = {FIXED [Driver ID] : MIN( IF [2nd Infraction] = [Infraction Date] THEN [Infraction Type] END ) }

• Antal typer av överträdelser = IF [1st Infraction Type]=[2nd Infraction Type] THEN 1 ELSEIF [1st Infraction Type]!= [2nd Infraction Type] THEN 2 ELSE 1 END

• Antal överträdelser = IF IFNULL([2nd Infraction Type], 'no') = 'no' THEN 1 ELSE 2 END

• Genomsnittligt bötesbelopp = SUM ([Total Amount Paid]) / SUM([Number of Infractions])

• 1:a körskolan = {FIXED [Driver ID] : MIN (IF [1st Infraction] = [Infraction Date] THEN [Traffic School] END ) }

• 2:a körskolan = {FIXED [Driver ID] : MIN (IF [2nd Infraction] = [Infraction Date] THEN [Traffic School] END ) }

• Antal körskolelektioner = (CASE [1st Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) + (CASE [2nd Traffic School] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END)