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
Comentarios
0 comentarios
Inicie sesión para dejar un comentario.