Dynamisk pivot-tabell

Det är inte ovanligt att man i QlikView har en pivot-tabell med cykliska dimensioner som låter användaren själv bestämma en kombination av olika kolumner. Cykliska grupper finns inte på samma sätt i Sense där man istället oftast arbetar med alternativa dimensioner. Just pivot-tabeller i Sense har inte stöd för alternativa dimensioner utan en annan lösning behöver hittas. I detta inlägg ska vi gå igenom hur dynamiska pivot-tabeller i Sense kan sättas upp.

I tabellen ovan kan användaren genom att klicka på respektive knapp med snurra välja vilket fält som ska visas i kolumnen. På detta sätt kan en pivot-tabell svara på ett mycket stort antal frågor.

Översikt

Vår lösning i Sense kommer att fungera som så att användaren får drop-down-menyer för respektive dynamisk kolumn i pivot-tabellen. Alternativen i menyerna kommer från scriptet och valt fält kommer att läggas i en variabel. Variabeln används sedan för att styra kolumnen i pivot-tabellen som sedan visas precis som den lagts dit manuellt.

Urvalsfält

Det första vi behöver göra är att i scriptet lägga till ett antal frikopplade fält som innehåller namnen på de kolumner som vi ska låta användaren välja mellan. I detta fall har vi listat de exakta fältnamnen, men det skulle också vara möjligt att utöka lösningen till att hantera separata visningsnamn och fältnamn.

Antal fält som vi sätter upp ska vara samma som antalet kolumner vi vill ha möjlighet att visa i pivot-tabellen. I det här exemplet kommer vi att använda tre kolumner, men detta kan enkelt utökas till fler.

I scriptet skapar vi först en tabell och läser sedan från den tre gånger för att skapa upp respektive fält. Namnen på fältet börjar med ett procent-tecken så att vi kan dölja dem från användaren med hjälp av HidePrefix.

Set HidePrefix = '%';

Dims:
Load rowno() as ReportDimID,* INLINE [
    ReportDim
    Country
    SalesOffice
    EmployeeID
    CustomerName
    City
    StateProvince
    ShipperName
    CategoryName
    ProductName
];

Dim1:
Load
    ReportDimID as %ReportDimID1,
    ReportDim as %ReportDim1
resident Dims;

Dim2:
Load
    ReportDimID as %ReportDimID2,
    ReportDim as %ReportDim2
resident Dims;

Dim3:
Load
    ReportDimID as %ReportDimID3,
    ReportDim as %ReportDim3
resident Dims;
drop table Dims;

Variabler

Nästa steg är att skapa tre variabler - en för varje fält som vi vill kunna lägga till i pivot-tabellen. Variablerna kommer vi att använda för att låta hämta värdet från drop-down-menyer koppade till urvalsfälten.

Drop-down-menyer

För att välja fält som ska visas i respektive kolumn kommer vi att använda drop-down-menyer vars värden består av kolumn-namnen - de värden som finns i våra rapportfält från scriptet. Lägg ut tre stycken Variable Input och ställ in dem enligt nedan. Variable Input hittar du under Custom Objects 🡒 Qlik Dashboard Bundle.

För en variable input lägger man till en variabel som kommer att få det valda värdet. Värdena som ska finnas att välja i drop-down-menyn sätter vi in som en dynamisk lista från fälten från scripten. I fälten ska värdena sättas in med ett pipe-tecken mellan sig, t.ex. City|Country|CustomerName.

Denna sammansättning av fältnamn kan vi skapa dynamiska genom att hämta fältvärdena i fälten från scripten och sätta ihop dem pipe-tecken mellan. Detta kan göras med concat()-funktionen. {1} i funktionen innebär att vi tar alla värden från fältet även om användaren på något sätt gjort ett urval. För den andra och tredje drop-down-menyn lägger vi också till ett alternativ "-" först i listan. Detta alternativ kommer vi att använda för att dölja kolumnen om användaren inte vill se den.

Dynamic values för de olika fälten:

1: =concat({1} %ReportDim1,'|')

2: ='-|'&concat({1} %ReportDim2,'|')

3: ='-|'&concat({1} %ReportDim3,'|')

Pivot-tabell

Nästa steg blir att skapa själva pivot-tabellen. Lägg till tre dimensioner i tabellen enligt nedan. Därefter kan det eller de uttryck som behövs läggas till.

Som fält hämtar vi variabelvärdet med $(vReportDim1) osv. Variabelvärdet är det fält som valts i dropdown-menyn, t.ex. Country. Vi sätter också värdet som etikett. För den andra och den tredje kolumnen lägger vi också på ett villkor för att kolumnen ska visas - visa endast om värdet i variabeln/drop-down inte är ett bindestreck. Detta döljer kolumnen om bindestreck är valt.

Den sista inställningen vi ska göra är att hantera ett fel som uppstår om användaren väljer samma fält i flera drop-down-menyer. För att göra detta lägger vi på ett beräkningsvillkor på tabellen och visar ett felmeddelande när samma fält valts. Villkoret som kontrollerar om samma fält valts:

(('$(vReportDim1)'='$(vReportDim2)') + ('$(vReportDim2)'='$(vReportDim3)') + ('$(vReportDim1)'='$(vReportDim3)'))=0

Uttrycket kollar variablerna parvis. Om något fält är lika kommer den jämförelsen ge svaret -1. Värdet av uttrycket kan alltså bli 0 (ingen är sann) eller under noll om någon eller alla av kolumnerna är lika.

Resultatet

Det vi nu fått fram är en pivot-tabell med tre dynamiska kolumner där användaren enkelt kan styra tabellens utseende.