La mancanza di alcune opzioni, soprattutto per chi viene da Qlikview, è una delle maggiori frustrazioni. Recentemente un cliente mi ha chiesto di poter ordinare delle misure su una pivot ed è sempre fastidioso dire “non si può”. Quindi ho cercato una soluzione ed ho trovato questa.
Iniziamo mettendo nello script le misure da caricare. Questo farà da repository delle misure e ci permetterà di semplificarci la vita se il cliente decide di cambiare delle logiche di calcolo (per dettagli vedere l’articolo sull’argomento):
Misure:
LOAD * INLINE [
Mis_Id,Mis_Label, Mis_Formula, Mis_Format
qta,Misura 1, sum(Expression1),"'#.##0'"
prztot,Molt 2 x 1, sum(Expression2)*sum(Expression1),"'#.##0'"
sconto,Terza,(sum(Expression1)+sum(Expression2))/sum(Expression3),"'#.##0,0'"
];Ricordarsi di inizializzare le due variabili, in modo che ci sia un sort iniziale corretto:
set vSortFld='qta';
set vSortVer=1;Invece che mettere direttamente il formato, si può anche usare una variabile, che aiuta se pensate possa rendersi necessario cambiare il formato dei numeri (cliente difficile eh?!). Io uso le doppie virgolette per non doverle esplicitare nella num() ma si possono omettere e usare la notazione semplice.
Ora si possono caricare i dati e impostare le misure con la funzione:
=num(
$(=only({<Mis_Id={qta}>} Mis_Formula)),
$(=only({<Mis_Id={qta}>} Mis_Format))
)
Se non si sono usate le doppie virgolette, ovviamente:
=num(
$(=only({<Mis_Id={qta}>} Mis_Formula)),
'$(=only({<Mis_Id={qta}>} Mis_Format))'
)Nella label della misura invece:
='$(=only({<Mis_Id={qta}>} Mis_Label))'Personalmente vi consiglio di usare un Id per le misure che identifichino per bene il significato. Quando infatti si guarda l’elenco delle misure, è utile poterle identificare per Id, se non significa niente dovete ogni volta andare ad aprire la funzione che è cosa scomoda (e non pensate che il posto della colonna sarà sempre quello, quindi non serve fare 1, 2, 3…. perché cambieranno di posto più e più volte!).
A questo punto possiamo pensare all’ordinamento.
In tutti i campi di dimensione occorre usare l’ordinamento personalizzato e ordinare in modo crescente per espressione. L’espressione da usare è la seguente:
=($(=only({<Mis_Id={$(=vSortFld)}>} Mis_Formula)) * $(vSortVer))Ovvero: sorting per il valore della formula selezionata moltiplicato per il verso d’ordine (1 o -1 = Ascend, Descend).
Per la selezione della formula da usare, usiamo i Pulsanti:
Dobbiamo fare ovviamente tanti pulsanti quante sono le misure, oppure si può fare con una finestra di filtro di selezione… ma eviterei per ora di pensare come comportarmi se vengono selezionate 2 formule…
Ad ogni pulsante si devono associare le 2 azioni nell’ordine corretto:
- Imposta valore variabile: dove si metterà per prima come nome variabile vSortVer con il seguente valore:
=IF(vSortFld=’qta’,vSortVer*-1,1)
Ovvero, se già siamo sul primo bottone, ogni volta che lo clicco, cambio il verso di sorting (per ogni bottone usare ovviamente l’Id corretto) - Imposta valore variabile: dove si metterà per seconda semplicemente il valore da far assumere alla variabile vSortFld (=’qta’, =’totprz’ ecc.).
Come etichetta del bottone invece occorre mettere una cosa del tipo:
=only({<Mis_Id={qta}>} Mis_Label) & if(vSortFld='qta',if(vSortVer=1,'▲','▼'))e sostituire ‘qta’ per ogni bottone con il codice corrispettivo (qta, prztot o sconto).
Questo è utile perché così possiamo decidere anche le label in modo centralizzato, e cambiandole sulla tabella, cambiano anche i bottoni e le label delle colonne della pivot in modo coerente.
Le icone servono a capire qual è il campo su cui è attivo il sorting e il verso. Per le icone io uso questo sito https://coolsymbol.com/ ma tutti vanno bene.
L’effetto finale è che quando si clicca su un bottone di sort, appare la freccia verso l’alto e poi cambia verso ogni volta che si clicca. La freccia sparisce quando si cambia campo, per mostrarsi sul bottone riferito al nuovo campo di sort attivo.
Miglioria: cambiare lo sfondo del bottone a seconda del campo con il sort attivo. Bello, ma occorre andare a scrivere un’altra funzione per ogni bottone, cosa che nel caso abbiate 15, 20 campi inizia ad essere un po’ impegnativo… ovviamente in questo caso consiglio di usare una variabile per il colore… trovate il daltonico o il creativo delle sfumature ed è un attimo sforare i tempi di consegna!
Risultato finale:
Come (non) funziona il sort sulle pivot
E qua si arriva alle dolenti note.
Purtroppo (o per fortuna) il sort sulle Pivot non funziona in modo contestuale alle dimensioni, ma in modo generale alla nuvola. Significa che se apriamo all’ultimo livello abbiamo una sorpresa:
I valori non sono apparentemente ordinati. Questo perché la Dim3 contiene descrizioni che sono in comune ad altre dimensioni (non succede se la gerarchia è solida e univoca). L’ordine con cui vengono presentati i numeri dell’ultimo livello è dato dal totale dei valori della dimensione in tutti i livelli. Cioè la sequenza d’ordine (nell’esempio) J, Z, S, H, Q….K è uguale in tutte le dimensioni.
Lo si vede bene girando la Pivot e mostrando la posizione in orizzontale dei numeri per vedere che i totali sono ordinati correttamente:
Ovviare a questo si può ma a discapito delle selezioni, infatti occorre rendere univoci i valori delle dimensioni all’interno delle gerarchie; nel nostro esempio, applicando questo sistema, l’effetto collaterale sarebbe che, se l’utente volesse eseguire una selezione per esempio sul valore “H” della Dim3, lo farebbe solo nell’ambito della Dim2=”A” e non su tutte le “H” presenti in Dim3, ovvero andrebbe a cambiare il comportamento “naturale” di Qlik.
Più semplice, nel caso ci si imbatta in questo problema (o meglio, lo si conosca in anticipo), spiegare la cosa al cliente ed esortarlo a spostare la dimensione da sortare al primo posto in modo da vedere i numeri corretti. Se ha delle gerarchie farlocche d’altronde non è del tutto colpa nostra e l’averlo avvisato prima evita discussioni.
Ovviamente questo problema non c’è se la gerarchia è corretta (del tipo che un codice prodotto sta solo sotto un’unica classe di prodotti).
Se vi interessa, per correggere l’errore, occorre fare una aggr() delle dimensioni. Trovate tutto qua nell’ottimo video di Christof Schwarz (secondo me è un po’ sporca, ma risolve, pur introducendo un problema grave di comportamento sui dati…).
A voi la scelta, intanto attendo commenti per migliorare questa idea.