Rullande snitt
Inom mätningar där värden av naturliga skäl varierar mycket vissa perioder tittar man ofta på ett rullande snitt tillsammans med periodens faktiska värde. Det rullande snittet beräknas som ett snitt av periodens och ett antal föregående perioders värden. Det rullande snittet varierar mindre och är lättare att följa över tid. Tänk dock på att snittet därmed visar förändring långsammare och om man bara tittar på det riskerar man att upptäcka förändring sent.
Rullande snitt kan beräknas på flera sätt på Qlik-plattformen och vi ska i det här inlägget titta på några av de vanligaste alternativen.
Det gamla sättet
Det första sättet som man räknade ett rullande snitt plockar man i grafen 12 punkter och räknar snittet. Så vid ett rullande 12 månaders snitt räknade man för punkten i månad 202012 snittet av alla punkter 202001 till och med 202012. Detta görs med funktionerna rangeavg() och above(). Above() plockar fram listan på punkter och Rangeavg() tar snittet av listan.
rangeavg(above(sum(Amount),0,12))
Above()-funktionen tar tre parametrar i detta fall:
Funktionen som ska räknas
Perioden man ska utgå från (0=aktuell period)
Antal perioder som ska hämtas
Så above(sum(Amount),0,12) hämtar en lista på sum(Amount) räknad över 12 dimensionsvärden (YearMonth) utifrån aktuell punkt.
Detta var ett enkelt sätt att räkna rullande 12 månaders snitt och beräkningen fungerar även om man gör urval på till exempel en produktgrupp och liknande. Dock fungerar den inte om man gör urval på tid då uttrycket endast utgår från de dimensionsvärden som finns i grafen. Om användaren gör ett urval på tid begränsas vilka dimensionsvärden som finns och uttrycket räknas om.
Studera värdena i den första grafen nedan. I datamodellen har vi egentligen data för 2018 med, men ett urval på 2019 har gjorts. I punkten 201901 sammanfaller det rullande snittet med periodens utfall. Detta beror på att vi tar ett snitt av endast en period. I 201902 tar vi snittet av 201901 och 201902 osv. Dessa värden hade inte varit samma om vi inte hade gjort ett urval på 2019. Jämför med den undre bilden.
Förbättring av Elif Tuk & Henric Cronström
Elif Tuk är en av Qliks riktiga stjärnor. Få som har pratat med henne eller hört henne tala kan ha missat hennes stora engagemang. Henric Cronström står bakom mycket av Qliks fantastiska funktionalitet. Som anställd nummer 3 på Qlik har han varit med på i princip hela resan. Om ni är på ett event av Qlik och får välja presentationer så välj de som dessa håller.
I var sitt blogginlägg från 2012 respektive 2015 beskriver Elif & Henrik förbättrade lösningar. Grundtanken är samma som ovan, men med tillägget att med hjälp av aggr och set-analys gå utanför användarens aktuella val och därmed kunna räkna med månader utanför urvalet. Om vi slår samman deras tankar kommer vi fram till följande formel:
only( aggr( rangeavg( above( sum( {$<YearMonth=>} Amount) + Sum({1} 0),0,12) ),YearMonth))
Som vi ser har uttrycket mycket gemensamt med det tidigare. Tillägget är att med aggr skapa en virtuell tabell över YearMonth och räkna sum(Amount) för denna. Uttrycket hanterar också tomma perioder genom ett tillägg av "+sum({1} 0)".
Uttrycket räknar nu rätt även om vi valt 2019 som år.
Drakes förbättring
Ett problem med uttrycket ovan är att användaren ofta kan göra urval i olika fält i kalendertabellen som gör att set-analysen inte fungerar. Till exempel om användaren väljer ett kvartal ignoreras inte detta urval med {$<YearMonth=}>.
Ett sätt att förbättra detta är att lista alla fält som måste ignoreras - Year, Quarter, YearQuarter, Month, YearMonth, Week... osv. Listan kan bli lång och det är lätt att glömma bort något fält som man lägger till i efterhand.
Ett bättre sätt kan du vara att generera listan av fält från Kalender-tabellen. Fältens namn finns i $Field och tabellnamn finns i $Table. Vi kan sätta samman värdena från Kalender-tabellen "Calendar" genom att använda concat-funktionen med set-analys på tabellnamnet.
concat({<$Table={Calendar}>} $Field, '=,') & '='
Detta sätter samman alla fält från Calendar-tabellen med ett lika-med-tecken - precis som syntaxen för att ignorera fält i set-analys är: Year=, YearMonth=, Month= osv.
För att göra detta vill vi först generera en sträng för hela uttrycket och sedan exekvera den. Detta gör vi med dollar sign-expansion $(='').
Insatt i huvuduttrycket blir detta:
$(='only( aggr( rangeavg( above( sum( {$<' & concat({<$Table={Calendar}>} $Field, '=,') & '=' & '>} Amount) + Sum({1} 0),0,12) ),YearMonth))')
Dynamiskt uttryck
Ibland vill man att användaren ska kunna välja antalet perioder som snittet ska beräknas med och användaren vill ibland också kunna byta dimension i grafen. Så användaren kan välja till exempel mellan rullande 12 månader eller rullande 10 veckor. För antalet perioder kan vi skapa en variabel med input-fält av något slag där användaren väljer värde. För dimensionen kan vi använda alternative dimensioner och funktionen GetObjectField(0).
Uttrycket blir då:
$(='only( aggr( rangeavg( above( sum( {$<' & concat({<$Table={Calendar}>} $Field, '=,') & '=' & '>} Amount) + Sum({1} 0),0,$(vNoOfPeriods)) ),' & GetObjectField(0) & '))')
Graferna nedan innehåller annan data än tidigare grafer och siffrorna överensstämmer därför ej.