Ufall vs Budget - En vanlig datamodell

Mycket ofta jämför vi i en Qlik-app något utfall mot en budget. Vi läser in dels filer som rör utfallet och filer som innehåller budget. Dessa två datamängder har vissa dimensioner gemensamma, t.ex. någon form av tidsperiod och produkt eller kund. Utfallsdata har en mycket högre granularitet - en mer detaljerad nivå - än budgetdata.

Så vad är bästa sättet att skapa en datamodell där vi vill jämföra utfall och budget med varandra? Vi kommer vilja ha grafer & tabeller där man visar och jämför utfall och budget. Vi har i grunden två alternativa modeller: länkade tabeller eller sammanfogad faktatabell.

Länkade tabeller

Länkade handlar som det låter om att vi länkar de två datamängderna vara två olika tabeller i minnet som länkar till varandra med någon form av nyckel. Nyckeln består oftast av de dimensioner som budgeten byggs upp med. Dessa dimensioner finns även i utfallet så att en nyckel kan skapas.

Budget:
Load
    ÅrMånad & '_' & Produkt as ÅrMånad_Produkt,
    ÅrMånad as ÅrMånad_Budget,
    Produkt as Produkt_Budget,
    Budget
from Budget.qvd (qvd);

Utfall:
Load
    ÅrMånad & '_' & Produkt as ÅrMånad_Produkt,
    OrderNr,
    ÅrMånad,
    Produkt,
    Utfall
from Utfall.qvd (qvd);
    

Mellan tabellerna finns det gemensamma nyckelfältet ÅrMånad_Produkt som är en kombination av de två fälten ÅrMånad och Produkt. Om det hade funnits fler gemensamma fält hade även dessa ingått i nycklen. Det beror alltså på vilken nivå ni lägger er budget.

Användaren kan ni göra urval på eller i grafer använda fälten ÅrMånad och Produkt från den undre utfallstabellen. Till exempel ett urval på Produkt=A väljer ut vissa rader i utfallstabellen och via de kvarvarande nyckelvärdena filtreras även budgettabellen.

Vad händer om vi i en graf vill visa budget för kommande framtida månader? Problemet vi får då är att fältet ÅrMånad endast innehåller de värden där det finns utfall. I exempeldata ovan saknas ÅrMånad 202003 i utfallet, men det finns i budget. Således kan inte graf visa budgetvärdet i 202003 och användaren kan heller inte göra ett urval på 202003.

För att lösa detta behöver vi flytta ut fälten till en nyckeltabell som innehåller kombinationen av värden.

Budget:
Load
    ÅrMånad & '_' & Produkt as ÅrMånad_Produkt,
    ÅrMånad as ÅrMånad_Budget,
    Produkt as Produkt_Budget,
    Budget
from Budget.qvd (qvd);

Utfall:
Load
    ÅrMånad & '_' & Produkt as ÅrMånad_Produkt,
    OrderNr,
    ÅrMånad as ÅrMånad_Utfall,
    Produkt as Produkt_Utfall,
    Utfall
from Utfall.qvd (qvd);

// Distinct will be applied to the 
// resulting table of concatenation

Budget_Utfall_Link:
Load distinct
    ÅrMånad_Produkt,
    ÅrMånad_Budget as ÅrMånad,
    Produkt_Budget as Produkt
resident Budget;

concatenate (Budget_Utfall_Link)
Load
    ÅrMånad_Produkt,
    ÅrMånad_Utfall as ÅrMånad,
    Produkt_Utfall as Produkt
resident Budget;

Notera att kommandot "distinct" i Budget_Utfall_Link bara sparar unika rader i den ihopslagna tabellen.

Metoden kan bli komplex när det är många fält som är gemensamma. Ännu mer så om det är flertalet tabeller inblandade med olika granularitet.

Sammanfogad faktatabell

Den andra metoden handlar om att att slå ihop faktatabellerna till en. På detta sätt hamnar gemensamma kolumner ihop och andra kolumner som bara finns i den ena tabellen får NULL-värden på rader från den andra tabellen.

Med denna metod behöver vi inte hantera nycklar eftersom det bara finns en gemensam tabell. Det kan dock vara bra att själv lägga till en kolumn som beskriver vilken radtyp som en rad motsvarar. Detta gör det praktiskt att felsöka samt som filter i uttryck där endast en viss typ av raderna behöver hanteras.

Fakta:
Load
    'Utfall' as Radtyp,
    OrderNr,
    ÅrMånad,
    Produkt,
    Utfall
from Utfall.qvd (qvd);

concatenate (Fakta)
Load
    'Budget' as Radtyp,
    ÅrMånad,
    Produkt,
    Budget
from Budget.qvd (qvd);

Detta är den enklaste och samtidigt oftast den smidigaste metoden. Finns olika typer av faktatabeller kan de oftast med fördel slås samman istället för att försöka nycklas samman.

Urval lägre nivå

Så vad händer om man gör ett urval på någon kolumn som inte finns i budget? Till exempel ett urval på ett visst datum, en kund eller ett ordernummer. I alla fallen ovan försvinner då budget-sifforna från det aktuella data. Detta eftersom det inte går att säga vilken budget som till exempel gäller för en viss kund.

Fördela budget

Ibland gör man budget på en högre nivå och väljer att matematiskt fördela ner budgeten. Det handlar oftast om tid, men kan även gälla till exempel produkter i en produktgrupp.

En månadsbudget kan fördelas på dagar i månaden genom till exempel att dela med antalet kalenderdagar eller antalet arbetsdagar eller genom att fördela med föregående års försäljning.

Här är ett exempel på kod där vi fördelar ut värdena rakt över alla kalenderdagar i månaden:

BudgetDistributed:
Load
	Produkt,
	date(date#(ÅrMånad,'YYYYMM')+IterNo()-1,'YYYY-MM-DD') as Date,
	Budget/day(monthend(date#(ÅrMånad,'YYYYMM'))) as BudgetDist
resident Budget 
	while