Lista de cálculos comparativos de Beast Mode

Cálculos de año contra año: comparación de varios períodos

Si se usa este tipo de comparación, todos los cálculos de coeficientes deben realizarse en un DataFlow.

Por año

Cree una gráfica y un cálculo anual en Beast Mode como categoría (eje X en la mayoría de los diagramas). 

year(`datefield`)

Por trimestre

Cree una gráfica y un cálculo trimestral en Beast Mode como categoría (eje X en la mayoría de los diagramas) y, si quiere abarcar varios años, use year(`datefield`) como serie.

Quarter(`datefield`)

Por mes

Cree una gráfica y un cálculo mensual en Beast Mode como categoría (eje X en la mayoría de los diagramas) y, si quiere abarcar varios años, use year(`datefield`) como serie.

Month(`datefield`)

Por semana

Cree una gráfica y un cálculo semanal en Beast Mode como categoría (eje X en la mayoría de los diagramas) y, si desea abarcar varios años, use year(`datefield`) como serie.

WeekofYear(`datefield`)

Por día

Cree una gráfica y un cálculo diario en Beast Mode como categoría (eje X en la mayoría de los diagramas) y, si desea abarcar varios años, use year(`datefield`) como serie.

DayofYear(`datefield`)

Cálculos de año contra año con cálculos de coeficientes: comparación entre dos períodos

"Este año" y "El año pasado" se pueden intercambiar para otros campos de texto o cantidades. Los campos de cantidades son necesarios para calcular coeficientes.

La ventaja de usar "Las mismas series" en lugar de "Varias series" es que puede consolidar el código con "Las mismas series". Sin embargo, si va a intercambiar "Este año"/"El año pasado" en las columnas de cantidades, estos valores deben estar separados.

Año contra año: comparación de año completo 

Varias series

Año actual

Case when Year(`Datefield`) = Year(curdate()) then 'This Year' end

Año anterior

Case when Year(`Datefield`) = Year(curdate())-1 then 'Last Year' end

Las mismas series

Case year(`Datefield`) when year(curdate()) then 'This Year' when year(curdate())-1 then 'Last Year’ End

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(Case when Year(`Datefield`) = Year(curdate()) then `Amountfield` end / Case when Year(`Datefield`) = Year(curdate())-1 then `Amountfield` end) -1

 

Año contra año: comparación de año a la fecha 

Varias series

Año actual

CASE  WHEN year(`Datefield`)=year(curdate())  THEN 'This Year' END

Año anterior

CASE  WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(curdate()) >= dayofyear(`Datefield`))) THEN 'Last Year' END

Las mismas series

CASE  year(`Datefield`) when year(curdate())  THEN 'This Year’ when ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(curdate()) >= dayofyear(`Datefield`))) THEN 'Last Year' END

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

 (CASE  WHEN year(`Datefield`)=year(curdate())  THEN `Amountfield` END / CASE  WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(curdate()) >= dayofyear(`Datefield`))) THEN `Amountfield` END) -1

Año contra año: comparación trimestral

Varias series

Año actual

Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' end 

Año anterior

Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1) then 'Last Year' end

Las mismas series

Case when (Quarter(`Datefield`) = Quarter(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (Quarter(`Datefield`) = Quarter(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())) then `Amountfield` end / Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1) then `Amountfield` end) -1

Año contra año: comparación de trimestre a la fecha

Varias series

Año actual

Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate()) and dayofyear(`Datefield`) <= dayofyear(curdate())) then 'This Year' end 

Año anterior

Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1 and dayofyear(`Datefield`) <= dayofyear(curdate())) then 'Last Year' end

Las mismas series

Case when (Quarter(`Datefield`) = Quarter(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (Quarter(`Datefield`) = Quarter(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate()) and dayofyear(`Datefield`) <= dayofyear(curdate())) then `Amountfield` end / Case when (quarter(`Datefield`) = quarter(curdate()) and year(`Datefield`) = year(curdate())-1 and dayofyear(`Datefield`) <= dayofyear(curdate())) then`Amountfield` end) -1

Año contra año: comparación mensual 

Varias series

Año actual

Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' end 

Año anterior

Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())-1) then 'Last Year' end

Las mismas series

Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())) then `Amountfield` end / Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate())-1) then `Amountfield` end) -1

Año contra año: comparación de mes a la fecha 

Varias series

Año actual

Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then 'This Year'  end

Año anterior

Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then  'Last Year'  end

Las mismas series

Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then 'This Year' when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then Last Year' End

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then `Amountfield` end / Case when (month(`Datefield`) = month(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate())) then `Amountfield`  end)  -1
 

Año contra año: comparación semanal

Varias series

Año actual

Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year’ end

Año anterior

Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End

Las mismas series

Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year’ End

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

((Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then `Amountfield` end / Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then `Amountfield` End) -1

Año contra año: comparación de semana a la fecha

Varias series

Año actual

Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then 'This Year' end

Año anterior

Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then 'Last Year' End

Las mismas series

Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then 'This Year' when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then 'Last Year’ End

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(curdate()) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then `Amountfield` end / Case when (WEEKOFYEAR(`Datefield`) = WEEKOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year)) and DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate())) then `Amountfield` End) -1

Año contra año: comparación diaria

Varias series

Año actual

Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' end

Año anterior

Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End

Las mismas series

Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then 'This Year' when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then 'Last Year' End

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

 (Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(curdate())) then `Amountfield` end / Case when (DAYOFYEAR(`Datefield`) = DAYOFYEAR(curdate()) and year(`Datefield`) = year(date_sub(curdate(), interval 1 year))) then `Amountfield` End) -1

Cálculos de período contra período con cálculos de coeficientes (este período frente al último)

Período contra período: comparación de trimestre contra trimestre

Varias series

Período actual

CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=quarter(CURDATE()))) THEN `Amountfield` END 

Período anterior

CASE  WHEN (quarter(curdate())=1) THEN (CASE  WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (quarter(`Datefield`)=4)) THEN `Amountfield` END ) ELSE (CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=(quarter(CURDATE()) -1))) THEN `Amountfield` END ) END

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=quarter(CURDATE()))) THEN `Amountfield` END / CASE  WHEN (quarter(curdate())=1) THEN (CASE  WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (quarter(`Datefield`)=4)) THEN `Amountfield` END ) ELSE (CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=(quarter(CURDATE()) -1))) THEN `Amountfield` END ) END) -1

Período contra período: comparación de trimestre contra trimestre de trimestre a la fecha

Varias series

Período actual

CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (quarter(`Datefield`)=quarter(CURDATE()))) THEN `Amountfield` END 

Período anterior

MAX(CASE WHEN YEAR(curdate()) / 4 = ROUND((YEAR(curdate()) / 4),0) THEN 

        CASE WHEN QUARTER(curdate())=1 THEN (DAYOFYEAR(curdate()) - 1)   

             WHEN (QUARTER(curdate())=2) THEN (DAYOFYEAR(curdate()) - 91) 

             WHEN (QUARTER(curdate())=3) THEN (DAYOFYEAR(curdate()) - 182)          

             WHEN (QUARTER(curdate())=4) THEN (DAYOFYEAR(curdate()) - 273) 

               ELSE 0 END 

       ELSE CASE 

            WHEN QUARTER(curdate())=1 THEN DAYOFYEAR(curdate()) - 1 

            WHEN QUARTER(curdate())=2 THEN DAYOFYEAR(curdate()) - 90 

            WHEN QUARTER(curdate())=3 THEN DAYOFYEAR(curdate()) - 181 

            WHEN QUARTER(curdate())=4 THEN DAYOFYEAR(curdate()) - 272 

               ELSE 0 END 

          END) * MAX(CASE WHEN YEAR(curdate()) / 4=ROUND(YEAR(curdate()) / 4,0) THEN 

              CASE WHEN (QUARTER(curdate())=1) THEN 91 

                    WHEN (QUARTER(curdate())=2) THEN 91 

                    WHEN (QUARTER(curdate())=3) THEN 92 

                          WHEN (QUARTER(curdate())=4) THEN 92 

               ELSE 0 END 

       ELSE CASE 

            WHEN (QUARTER(curdate())=1) THEN 90   

                    WHEN (QUARTER(curdate())=2) THEN 91 

                    WHEN (QUARTER(curdate())=3) THEN 92 

                          WHEN (QUARTER(curdate())=4) THEN 92 

               ELSE 0 END 

          END) - MAX(CASE WHEN YEAR(curdate()) / 4=ROUND(YEAR(curdate()) / 4,0) THEN        

         CASE WHEN QUARTER(curdate())=1 THEN DAYOFYEAR(curdate()) - 1

                   WHEN QUARTER(curdate())=2 THEN DAYOFYEAR(curdate()) - 91 

                 WHEN QUARTER(curdate())=3 THEN DAYOFYEAR(curdate()) - 182 

                   WHEN QUARTER(curdate())=4 THEN DAYOFYEAR(curdate()) - 273 

               ELSE 0 END 

       ELSE CASE 

            WHEN QUARTER(curdate())=1 THEN DAYOFYEAR(curdate()) - 1 

            WHEN QUARTER(curdate())=2 THEN DAYOFYEAR(curdate()) - 90 

            WHEN QUARTER(curdate())=3 THEN DAYOFYEAR(curdate()) - 181 

            WHEN QUARTER(curdate())=4 THEN DAYOFYEAR(curdate()) - 272 

               ELSE 0 END 

END) + SUM(CASE WHEN QUARTER(curdate())=QUARTER(`Datefield`) AND YEAR(`Datefield`)=YEAR(curdate()) THEN `Amountfield` ELSE 0 END) - 

SUM((CASE WHEN ((QUARTER(curdate())=QUARTER(`Datefield`)) AND (YEAR(`Datefield`)=YEAR(DATE_SUB(curdate(),INTERVAL 1 QUARTER)))) THEN `Amountfield` ELSE 0 END ))) / SUM((CASE  WHEN QUARTER(DATE_SUB(curdate(),INTERVAL 1 QUARTER))=QUARTER(`Datefield`) AND YEAR(`Datefield`)=YEAR(DATE_SUB(curdate(),INTERVAL 1 QUARTER)) THEN `Amountfield` ELSE 0 END ))

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

Use la fórmula anterior e inserte las dos secciones anteriores para obtener el resultado.

Período contra período: comparación de mes contra mes

Varias series

CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=month(CURDATE()))) THEN `Amountfield` END 

CASE  WHEN (month(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (month(`Datefield`)=12)) THEN `Amountfield` END ) ELSE (CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=(month(CURDATE()) -1))) THEN `Amountfield` END ) END

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=month(CURDATE()))) THEN `Amountfield` END / CASE  WHEN (month(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (month(`Datefield`)=12)) THEN `Amountfield` END ) ELSE (CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=(month(CURDATE()) -1))) THEN `Amountfield` END ) END) -1

Período contra período: comparación de mes contra mes de mes a la fecha

Varias series

CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=month(CURDATE()))) THEN `Amountfield` END 

CASE WHEN month(curdate()) = 1 THEN CASE WHEN year(`Datefield`) = year(curdate()) - 1 AND month(`Datefield`) = 12 AND DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate()) THEN `Amountfield` END  ELSE CASE  WHEN year(`Datefield`) = year(curdate()) AND month(`Datefield`) = month(curdate())-1 AND DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate()) THEN `Amountfield` END END

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (month(`Datefield`)=month(CURDATE()))) THEN `Amountfield` END / CASE WHEN month(curdate()) = 1 THEN CASE WHEN year(`Datefield`) = year(curdate()) - 1 AND month(`Datefield`) = 12 AND DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate()) THEN `Amountfield` END  ELSE CASE  WHEN year(`Datefield`) = year(curdate()) AND month(`Datefield`) = month(curdate())-1 AND DAYOFMONTH(`Datefield`) <= DAYOFMONTH(curdate()) THEN `Amountfield` END END) -1

Período contra período: comparación de semana contra semana

Varias series

CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=weekofyear(CURDATE()))) THEN `Amountfield` END 

CASE  WHEN (weekofyear(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (weekofyear(`Datefield`)=52)) THEN `Amountfield` END ) ELSE (CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=(weekofyear(CURDATE()) -1))) THEN `Amountfield` END ) END

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1 

(CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=weekofyear(CURDATE()))) THEN `Amountfield` END / CASE  WHEN (weekofyear(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (weekofyear(`Datefield`)=52)) THEN `Amountfield` END ) ELSE (CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=(weekofyear(CURDATE()) -1))) THEN `Amountfield` END ) END) -1

Período contra período: comparación de semana contra semana de semana a la fecha

Varias series

CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=weekofyear(CURDATE()))) THEN `Amountfield` END 

CASE WHEN weekofyear(curdate()) = 1 THEN CASE WHEN year(`Datefield`) = year(curdate()) - 1 AND weekofyear(`Datefield`) = 52 AND DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate()) THEN `Amountfield` END ELSE CASE WHEN year(`Datefield`) = year(curdate()) AND weekofyear(`Datefield`) = weekofyear(CURDATE()) -1 AND DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate()) THEN `Amountfield` END END

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (weekofyear(`Datefield`)=weekofyear(CURDATE()))) THEN `Amountfield` END / CASE WHEN weekofyear(curdate()) = 1 THEN CASE WHEN year(`Datefield`) = year(curdate()) - 1 AND weekofyear(`Datefield`) = 52 AND DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate()) THEN `Amountfield` END ELSE CASE WHEN year(`Datefield`) = year(curdate()) AND weekofyear(`Datefield`) = weekofyear(CURDATE()) -1 AND DAYOFWEEK(`Datefield`) <= DAYOFWEEK(curdate()) THEN `Amountfield` END END) -1

Período contra período: comparación de día contra día

Varias series

CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (dayofyear(`Datefield`)=dayofyear(CURDATE()))) THEN `Amountfield` END 

CASE  WHEN (dayofyear(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(`Datefield`)=365)) THEN `Amountfield` END ) ELSE (CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (dayofyear(`Datefield`)=(dayofyear(CURDATE()) -1))) THEN `Amountfield` END ) END

Coeficiente: % de cambio entre los dos con la fórmula (C/P) -1

(CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (dayofyear(`Datefield`)=dayofyear(CURDATE()))) THEN `Amountfield` END / CASE  WHEN (dayofyear(curdate())=1) THEN (CASE WHEN ((year(`Datefield`)=(year(curdate()) - 1)) AND (dayofyear(`Datefield`)=365)) THEN `Amountfield` END ) ELSE (CASE  WHEN ((year(`Datefield`)=year(curdate())) AND (dayofyear(`Datefield`)=(dayofyear(CURDATE()) -1))) THEN `Amountfield` END ) END) -1