MondoBI

MondoBI è un forum italiano dedicato alla Business Intelligence. Domande e risposte su Power BI, Tableau, Excel, SQL... e non solo!
https://www.mondobi.it/

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

https://www.mondobi.it/viewtopic.php?t=1052

Pagina 1 di 2

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: lun 24 apr 2023, 14:53
da Aleior80
Ciao a tutti, ho un quesito che mi fa dannare da qualche giorno che vi riporto di seguito:

il mio data model di esempio è composto da una Dim Table di Calendario e da una Fact Table così strutturata:

Immagine

lo scopo è quello di calcolare l'andamento per anno/mese etc. dei ticket in Backlog , conteggiando quanti ID hanno la Data Chiusura blank oppure successiva a quella massima del filter context.
Per questi ID mi occorre poi calcolare per l'Aging (tempo di lavorazione) medio, ossia la differenza tra la massima data dal filter context e la Data Apertura, diviso il numero di ticket in backlog calcolato in precedenza, il tutto possibilmente in giorni lavorativi lun-ven (la Dim di calendario dispone di una comoda colonna "weekend" popolata con valori Y/N).

il primo problema (calcolo Backlog) sono riuscito a risolverlo con la seguente misura dax:

Immagine

ottenendo il seguente risultato:
Immagine

per il calcolo dell'aging, ad esempio per il mese di marzo, occorrerebbe, per tutti gli ID che hanno la data chiusura blank o successiva al 31/3, calcolare la differenza tra la data del 31 marzo e la data di apertura dell'ID, rapportandola al conteggio dei ticket in backlog al 31/3.

Ho provato varie strade ma niente...ringrazio infinitamente chi perderà un po' del proprio tempo per un suggerimento :wave:

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: lun 24 apr 2023, 17:56
da Andrea90
Aleior80,

Sinceramente non è chiarissimo ciò che vorresti ottenere.

Conviene girare anche un file excel con la logica di calcolo da applicare per una sola combinazione di Anno - Mese.

Comunque nel mentre allego una misura con quel poco che sono riuscito a comprendere:

Codice: Seleziona tutto

Test = 
VAR MaxDate =
    MAX ( DimCalendario[Date] )
VAR TempTab =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                FactTicket,
                OR (
                    ISBLANK ( FactTicket[Data Chiusura] ),
                    FactTicket[Data Chiusura] > MaxDate
                )
            ),
            FactTicket[Data Apertura]
        ),
        "@Cnt", CALCULATE ( COUNT ( FactTicket[ID] ) ),
        "@DiffDate",
            COUNTROWS (
                FILTER (
                    DimCalendario,
                    DimCalendario[Date] >= FactTicket[Data Apertura]
                        && DimCalendario[Date] <= MaxDate
                        && DimCalendario[Weekend] = "N"
                )
            )
    )
VAR Result = DIVIDE( SUMX ( TempTab, [@Cnt] * [@DiffDate] ), [N.ObID_Backlog] )
RETURN
    Result
Andrea

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: mar 25 apr 2023, 13:18
da Aleior80
Andrea90, innanzitutto grazie infinite per la disponibilità, provo a spiegarti meglio il risultato che vorrei raggiungere.

La Fact Table è formata da un elenco di ID ticket, per ciascuno dei quali ho la data di creazione, la data di chiusura che può essere popolata o meno a seconda dello stato (in lavorazione/chiuso)

Con la misura che ho riportato sopra (N.ObID_Backlog), riesco ad isolare i ticket che sono inevasi, ossia che hanno Data di creazione entro il termine del mese e non hanno popolata la data di chiusura oppure, laddove questa sia presente, è successiva all'ultimo giorno del mese in questione.

Ad esempio, per marzo 2023, ho 1.440 id in backlog (666 con data chiusura blank + 774 con data chiusura successiva al 31/3).

Al totale backlog vorrei associare il tempo di lavorazione (aging) medio, calcolato come somma dei giorni che vanno dalla data di creazione all'ultimo giorno del mese in esame, diviso il N.ObID_Backlog.
Per la somma dei giorni di lavorazione devo sempre tenere conto degli ID creati entro la fine del mese ed escludere quelli con data chiusura blank o successiva al termine del mese.

Ad esempio, sempre per il mese di marzo, i 1.440 ticket in backlog hanno un tempo totale di lavorazione di 110.959 giorni (giorni compresi tra il 31 marzo e la rispettiva data di creazione). Quindi con un aging medio di 110.959/1.440 = 77 giorni solari

per febbraio 2023, i 441 ticket in backlog hanno un tempo totale di lavorazione, al 28/2 (giorni tra il 28/2 e data di creazione) di 90.094, con un aging medio di 90.094/441 = 183 giorni solari

ti allego la fact da dove ho ricavato i due esempi (la trovi comunque anche all'interno di pbix di test)
Ho provato con la tua misura, ma i valori che restituisce sono purtroppo troppo bassi :(

Ciao e grazie ancora

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: mar 25 apr 2023, 14:58
da Andrea90
Aleior80,

Prova cosi:

Codice: Seleziona tutto

Test = 
VAR MaxDate =
    MAX ( DimCalendario[Date] )
VAR TempTab =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALL( FactTicket ),
                OR (
                    ISBLANK ( FactTicket[Data Chiusura] ) && FactTicket[Data Apertura] <= MaxDate ,
                    FactTicket[Data Chiusura] > MaxDate
                )
            ),
            FactTicket[ID], FactTicket[Data Apertura]
        ),
        "@DiffDate",
            COUNTROWS (
                FILTER (
                    ALL(DimCalendario),
                    DimCalendario[Date] > FactTicket[Data Apertura]
                        && DimCalendario[Date] <= MaxDate
                        -- && DimCalendario[Weekend] = "N"
                ) 
            )
    )
VAR Result = DIVIDE( SUMX ( TempTab, [@DiffDate] ), [N.ObID_Backlog] )
RETURN
    Result
Questo conteggio dovrebbe riportare il numero da te indicato per marzo, ma così facendo consideri anche i giorni del weekend, mentre se non vuoi considerarli devi rimuovere la stringa di commento che ho lasciato dentro al calcolo del @DiffDate.

Andrea

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: mar 25 apr 2023, 17:28
da Aleior80
Andrea90 direi che va alla grande! :clap:

Grazie infinite! :thumbup: :D

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: mer 26 apr 2023, 9:15
da Andrea90
Aleior80,

Giusto come prova, questa nuova misura dovrebbe restituire lo stesso risultato di quella precedente, ma in termini di performance dovrebbe essere superiore:

Codice: Seleziona tutto

Test = 
VAR CurDate =
    MAX ( DimCalendario[Date] )
VAR TempTab =
    ADDCOLUMNS (
        GROUPBY (
            SUMMARIZE (
                FILTER (
                    ALL ( FactTicket ),
                    FactTicket[Data Apertura] <= CurDate
                        && OR (
                            ISBLANK ( FactTicket[Data Chiusura] ),
                            FactTicket[Data Chiusura] > CurDate
                        )
                ),
                FactTicket[Data Apertura],
                FactTicket[ID]
            ),
            FactTicket[Data Apertura],
            "@Cnt", COUNTX ( CURRENTGROUP (), 1 )
        ),
        "@DiffDate",
            COUNTROWS (
                FILTER (
                    ALL ( DimCalendario ),
                    DimCalendario[Date] > FactTicket[Data Apertura]
                        && DimCalendario[Date] <= CurDate -- && DimCalendario[Weekend] = "N"
                )
            )
    )
VAR Result = DIVIDE( SUMX ( TempTab, [@Cnt] * [@DiffDate] ), [N.ObID_Backlog] )
RETURN
    Result
Andrea

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: gio 27 apr 2023, 13:22
da Aleior80
Grazie Andrea! effettivamente sembra più rapida, però noto che c'è un problema per il Backlog del mese in corso, provo a spiegarmi meglio.

Nell'esempio che avevo postato i dati si fermavano a marzo, quindi il tempo di lavorazione veniva correttamente calcolato come differenza tra la fine del periodo (max sulla data di calendario) e la data di apertura.

Se però il mese non è concluso l'estremo superiore dell'intervallo di tempo non deve essere l'ultimo giorno del periodo (mese/settimana etc.), ma Today.
Ad esempio, i 945 ticket in backlog di aprile (N.OBID_Backlog) ad oggi, hanno un tempo di lavorazione di 16.767 giorni (differenza tra today e data apertura e non tra 30/4 e data apertura) con un aging medio di 18 gg. solari.

Ti allego un esempio con i dati popolati anche per il mese di aprile, è leggermente differente la fact, gli id sono ripetuti per ogni cambio stato ed è presente la colonna Ultimo stato (un flag Y/N che ne identifica l'ultimo).
Ho già modificato la tua misura di Test, inserendo alla riga 10 del Filter della Summarize la condizione di Ultimo Stato=Y e funziona correttamente,

Codice: Seleziona tutto

Test = 
VAR CurDate =
    MAX ( DimCalendario[Date] )
VAR TempTab =
    ADDCOLUMNS (
        GROUPBY (
            SUMMARIZE (
                FILTER (
                    ALL ( FactTicket ),
                    FactTicket[Ultimo Stato]="Y"
                    &&
                    FactTicket[Data Apertura] <= CurDate
                        && OR (
                            ISBLANK ( FactTicket[Data Chiusura] ),
                            FactTicket[Data Chiusura] > CurDate
                        )
                ),
                FactTicket[Data Apertura],
                FactTicket[ID]
            ),
            FactTicket[Data Apertura],
            "@Cnt", COUNTX ( CURRENTGROUP (), 1 )
        ),
        "@DiffDate",
            COUNTROWS (
                FILTER (
                    ALL ( DimCalendario ),
                    DimCalendario[Date] > FactTicket[Data Apertura]
                        && DimCalendario[Date] <= CurDate -- && DimCalendario[Weekend] = "N"
                )
            )
    )
VAR Result = DIVIDE( SUMX ( TempTab, [@Cnt] * [@DiffDate] ), [N.ObID_Backlog] )
RETURN
    Result
Ti ringrazio davvero! :thumbup:

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: gio 27 apr 2023, 14:16
da Andrea90
Aleior80,

Se il tuo unico problema è fissare in maniera dinamica la data della variabile CurDate puoi semplicemente impostare la logica che meglio preferisci per determinarla, si tratta di semplici condizioni IF().

Andrea

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: gio 27 apr 2023, 17:58
da Aleior80
grazie per lo spunto Andrea90 :thumbup:

come hai suggerito, ho risolto con un semplice IF nella current date, lascio il codice completo eventualmente dovesse servire a qualcuno in futuro:

Codice: Seleziona tutto

Test = 
VAR CurDate =
    IF(
        TODAY() < MAX (DimCalendario[Date]),
        TODAY(),
        MAX ( DimCalendario[Date] )
    )
VAR TempTab =
    ADDCOLUMNS (
        GROUPBY (
            SUMMARIZE (
                FILTER (
                    ALL ( FactTicket ),
                    FactTicket[Ultimo Stato]="Y"
                    &&
                    FactTicket[Data Apertura] <= CurDate
                        && OR (
                            ISBLANK ( FactTicket[Data Chiusura] ),
                            FactTicket[Data Chiusura] > CurDate
                        )
                ),
                FactTicket[Data Apertura],
                FactTicket[ID]
            ),
            FactTicket[Data Apertura],
            "@Cnt", COUNTX ( CURRENTGROUP (), 1 )
        ),
        "@DiffDate",
            COUNTROWS (
                FILTER (
                    ALL ( DimCalendario ),
                    DimCalendario[Date] > FactTicket[Data Apertura]
                        && DimCalendario[Date] <= CurDate -- && DimCalendario[Weekend] = "N"
                )
            )
    )
VAR Result = DIVIDE( SUMX ( TempTab, [@Cnt] * [@DiffDate] ), [N.ObID_Backlog] )
RETURN
    Result

Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Inviato: gio 27 apr 2023, 18:06
da Andrea90
Aleior80,

Ti consiglio di cominciare a far più largo uso delle variabili perché oltre che a rendere il codice più pulito, lo rendono anche più performante.

Ad esempio nella tua IF clause, tu calcoli il MAX e TODAY due volte. Se invece salvavi il risultato di TODAY e di MAX in due variabili distinte avresti potuto richiamare queste ultime senza la necessità di dover eseguire lo stesso calcolo più volte.

Andrea
Tutti gli orari sono UTC+01:00
Pagina 1 di 2

Creato da phpBB® Forum Software © phpBB Limited

Traduzione Italiana phpBB-Italia.it