PeriodOverPeriodComparison - Desenvolvimento QuickSight

Q&A
, , , , ,
1

I am working on a dataset consolidating information from different APIs (SCR, SPC, MARGEM_CONSIGNAVEL, OPEN_FINANCE) and trying to calculate accumulated totals and percent variation directly in QuickSight.

Dataset Structure / Estrutura do Dataset:
I have a view in Athena/SQL structured as follows:

  • Daily counts CTEs / CTEs de contagem diária: scr_counts, spc_counts, margemconsignavel_cte, and openfinance_cte calculate the distinct number of CPFs (total_dia) per day (data) for each API.

  • Unified dataset / Dataset unificado: all_counts merges all daily counts.

  • Monthly total / Acumulado mensal: monthly_total sums total_dia by API per month (total_mes).

  • Cost calculation / Cálculo de custo: calculated directly using CASE WHEN in the view to assign custo_por_registro and custo_total_dia based on API and monthly totals.

Example of resulting dataset / Exemplo do dataset resultante:

# data api total_dia total_mes custo_por_registro custo_total_dia variacao_percentual
1 2025-08-12 MARGEM_CONSIGNAVEL 1 37859 0.08 0.08
2 2025-08-13 MARGEM_CONSIGNAVEL 419 37859 0.08 33.52 41800
3 2025-08-14 MARGEM_CONSIGNAVEL 109 37859 0.08 8.72 0
4 2025-08-18 MARGEM_CONSIGNAVEL 163 37859 0.08 13.04 0
… … … … … … … …

Fields Already Created in QuickSight / Campos já criados no QuickSight:

Filtered Period Total / Acumulado do período filtrado:

totalAcumuladoPeriodoFiltrado =
sumIf(
    {total_dia},
    truncDate('DD', {data}) >= truncDate('DD', ${pDiaInicio})
    AND truncDate('DD', {data}) <= truncDate('DD', ${pDiaFim})
)

Previous Period Dates / Datas do período anterior:

dataInicioPeriodoAnterior =
addDateTime(-duracaoPeriodoDias, 'DD', truncDate('DD', ${pDiaInicio}))

dataFimPeriodoAnterior =
addDateTime(-1, 'DD', truncDate('DD', ${pDiaInicio}))

duracaoPeriodoDias =
abs(
    dateDiff(truncDate('DD', ${pDiaFim}), truncDate('DD', ${pDiaInicio}), 'DD')
) + 1

Percent Variation / Variação percentual:

variacaoPercentualCalculada =
ifelse(
    ${totalAcumuladoPeriodoAnterior} != 0,
    (${totalAcumuladoPeriodoFiltrado} - ${totalAcumuladoPeriodoAnterior}) / ${totalAcumuladoPeriodoAnterior} * 100,
    NULL
)

Problem Encountered / Problema Encontrado:
When trying to calculate the previous period total / Ao tentar criar o total acumulado do período anterior:

totalAcumuladoPeriodoAnterior =
sumIf(
    {total_dia},
    truncDate('DD', {data}) >= addDateTime(-${duracaoPeriodoDias}, 'DD', truncDate('DD', ${pDiaInicio}))
    AND truncDate('DD', {data}) <= addDateTime(-1, 'DD', truncDate('DD', ${pDiaInicio}))
)

  • The result is always empty (zero) / sempre vazio (zero).

  • Likely because QuickSight cannot resolve other calculated fields like duracaoPeriodoDias inside sumIf.

  • Everything needs to be inline within the sumIf to work correctly / Tudo precisa estar inline dentro do sumIf para funcionar corretamente.

What I Still Need / O que ainda preciso criar:

  • A working totalAcumuladoPeriodoAnterior calculation in QuickSight that sums total_dia for the corresponding previous period / Um cálculo funcional de totalAcumuladoPeriodoAnterior somando total_dia no período anterior correspondente.

  • After that, variacaoPercentualCalculada should compute correctly / Depois disso, variacaoPercentualCalculada poderá ser calculada corretamente.

  • The solution should avoid relying on intermediate calculated fields inside sumIf and use inline date calculations / A solução ideal deve evitar campos calculados intermediários dentro do sumIf e usar cálculos de data inline.

Goal / Objetivo:

  • Compare current vs previous period totals per API / Comparar totais do período atual vs período anterior por API.

  • Display percent variation as KPI or chart in QuickSight / Exibir a variação percentual como KPI ou gráfico no QuickSight.

2

Hi @Gustavo_Souza and welcome to the Quick Suite Community!
Take a look at the attached documentation below, here are some old posts that explore period over period comparisons a bit further in depth. Let me know if either of these assist with your case: