Calcolo andamento Aging (tempo lavorazione ticket inevasi)

Aleior80 · Messaggio da **Aleior80** » lun 24 apr 2023, 14:53

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

Topic creati · Messaggio da **Andrea90** » lun 24 apr 2023, 17:56

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

Aleior80 · Messaggio da **Aleior80** » mar 25 apr 2023, 13:18

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

Topic creati · Messaggio da **Andrea90** » mar 25 apr 2023, 14:58

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

Aleior80 · Messaggio da **Aleior80** » mar 25 apr 2023, 17:28

Andrea90 direi che va alla grande!

Grazie infinite!

Topic creati · Messaggio da **Andrea90** » mer 26 apr 2023, 9:15

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

Aleior80 · Messaggio da **Aleior80** » gio 27 apr 2023, 13:22

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!

Topic creati · Messaggio da **Andrea90** » gio 27 apr 2023, 14:16

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

Aleior80 · Messaggio da **Aleior80** » gio 27 apr 2023, 17:58

grazie per lo spunto Andrea90

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

Topic creati · Messaggio da **Andrea90** » gio 27 apr 2023, 18:06

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