Linguagem de expressões

Tipo: Referência
Para: Analista, Desenvolvedor

Uma expressão é uma medida ou um agrupamento calculado na Aplicação, escrito em SQL do banco do Lumo (Apache Doris) com algumas extensões: use() para trocar a chave de uma ligação e ${...} para calcular com outro filtro. Uma medida também pode ser escrita em DAX. A expressão é calculada na hora da consulta, sem gravar nada no HorusDW. Como criar uma na tela está em Expressões.

"Expressão" aqui é só a fórmula da aba Expressões. As regras em JavaScript de cor e texto dos Widgets são outra coisa, descrita em Regras em JavaScript.

Referências

O que referenciar	Sintaxe	Exemplo
Coluna de tabela, pelo nome da coluna	`[Tabela]."COLUNA"`	`[Fato Vendas]."VALOR_TOTAL"`
Coluna de tabela, pelo rótulo (vale também para a expressão de tabela do HorusDW)	`[Tabela].[Rótulo]`	`[Funcionários].[Tempo de Casa]`
Outra expressão da Aplicação	`["Nome da Expressão"]`	`["Faturamento Realizado"]`

O nome da coluna vai entre aspas duplas. [Vendas].VALOR, sem aspas, não é reconhecido como coluna (a exceção é dentro de use(), logo abaixo).

Valem as agregações e operadores do SQL: SUM, COUNT, AVG, MIN, MAX, COUNT(DISTINCT ...), + - * / e CASE WHEN ... THEN ... ELSE ... END.

sql

-- Medida: percentual de meta atingida
["Faturamento Realizado"] / ["Meta de Faturamento"] * 100

-- Agrupamento: faixa de valor
CASE
    WHEN [Vendas]."VALOR" > 1000 THEN 'Alto'
    WHEN [Vendas]."VALOR" > 500 THEN 'Médio'
    ELSE 'Baixo'
END

Dividir uma expressão de Vendas por uma de Metas é seguro porque cada fato é agregada na sua consulta antes da conta. O motivo está em Modelo de dados.

Uma razão (ticket médio, margem) deve ser expressão, não coluna gravada por linha no HorusDW: somar ou tirar a média de razões por linha dá o número errado. Coluna no HorusDW serve para conta que vale linha a linha, como QUANTIDADE * PRECO_UNITARIO.

use(): data alternativa

use() faz a expressão seguir uma chave secundária da ligação no lugar da padrão.

sql

use([TabelaDimensão], [TabelaFato].COLUNA_ALTERNATIVA) EXPRESSÃO

sql

-- Valor das oportunidades pela data de ganho, e não pela data padrão da ligação
use([Calendário], [Oportunidades].DATA_GANHO) SUM([Oportunidades]."VALOR")

Regra	Detalhe
Posição	`use()` vem no começo da fórmula
Chave	A coluna precisa estar cadastrada como chave secundária da ligação (como cadastrar)
Coluna	Coluna física da tabela, com ou sem aspas (`DATA_GANHO` ou `"DATA_GANHO"`). Expressão não serve
Uma por vez	Troca uma coluna. Numa ligação de chave composta, as outras colunas continuam valendo
Erros	`Coluna use(..., X) não encontrada na aplicação.`, `Tabela use(X, ...) não encontrada na aplicação.`, `Coluna use(X) é ambígua em várias tabelas: ...`

${...}: calcular com outro filtro

${ EXPRESSÃO, FILTRO1, FILTRO2, ... } calcula a expressão com os filtros escritos ali, que substituem os filtros da tela nessa expressão. Serve para ano anterior, acumulado do ano e participação sobre o total.

Operadores que funcionam nos filtros: =, IN, LIKE, BETWEEN, e > e < com espaço antes e depois. Evite !=, >=, <=, NOT IN e NOT LIKE: o leitor do filtro acha primeiro o =, o IN ou o LIKE de dentro deles e parte a condição no lugar errado. Para um intervalo, use BETWEEN.

Ler o filtro da tela com <...>

Dentro de ${...}, <[Tabela]."Coluna", padrão> devolve o valor que o leitor filtrou nesse campo ou, sem filtro, o padrão. Em campo de data, :START e :END devolvem o começo e o fim do período filtrado:

Filtro na tela	`:START` e `:END`
`BETWEEN`	A data inicial e a final
Relativo, como "Últimos 30 Dias"	As datas calculadas do período
`IN` com datas	A menor e a maior data
Nenhum	O valor padrão

Exemplos

Mesmo período do ano anterior:

sql

${
    SUM([Fato Vendas]."Valor Total"),
    [Calendário]."DATA" BETWEEN
        ADDYEARS(<[Calendário]."DATA":START, CURRENT_DATE>, -1)
        AND ADDYEARS(<[Calendário]."DATA":END, CURRENT_DATE>, -1)
}

Acumulado do ano até o fim do período filtrado:

sql

${
    SUM([Fato Vendas]."Valor Total"),
    [Calendário]."DATA" BETWEEN
        YEARSTART(<[Calendário]."DATA":START, CURRENT_DATE>)
        AND <[Calendário]."DATA":END, CURRENT_DATE>
}

Mês anterior inteiro:

sql

${
    SUM([Fato Vendas]."Valor Total"),
    [Calendário]."DATA" BETWEEN
        MONTHSTART(ADDMONTHS(<[Calendário]."DATA":START, CURRENT_DATE>, -1))
        AND MONTHEND(ADDMONTHS(<[Calendário]."DATA":END, CURRENT_DATE>, -1))
}

Para fazer cada Widget responder na própria granularidade (o mês inteiro do dia escolhido, os últimos 12 meses), veja Calendário e filtros por data.

Funções de data

Valem dentro de ${...} e <...>. A constante CURRENT_DATE também.

Função	Retorna
`ADDDAYS(data, dias)`	A data mais N dias
`ADDMONTHS(data, meses)`	A data mais N meses
`ADDYEARS(data, anos)`	A data mais N anos
`SUBDAYS(data, dias)`	A data menos N dias
`SUBMONTHS(data, meses)`	A data menos N meses
`SUBYEARS(data, anos)`	A data menos N anos
`MONTHSTART(data)`	Primeiro dia do mês. Alias: `FIRSTDAYOFMONTH`
`MONTHEND(data)`	Último dia do mês. Alias: `LASTDAYOFMONTH`
`YEARSTART(data)`	Primeiro dia do ano
`YEAREND(data)`	Último dia do ano
`QUARTERSTART(data)`	Primeiro dia do trimestre
`QUARTEREND(data)`	Último dia do trimestre
`WEEKSTART(data)`	Primeiro dia da semana (domingo)
`WEEKEND(data)`	Último dia da semana (sábado)
`DAYOFWEEK(data)`	Dia da semana, de 0 (domingo) a 6 (sábado)
`DAYOFMONTH(data)`	Dia do mês, de 1 a 31
`MONTH(data)`	Mês, de 1 a 12
`YEAR(data)`	Ano
`DATEDIFF(data1, data2)`	Diferença em dias
`TODAY()`	Data de hoje
`MIN(valor1, ...)`	Menor valor da lista
`MAX(valor1, ...)`	Maior valor da lista

Medidas em DAX

Uma medida também pode ser escrita em DAX, a linguagem de fórmulas do Power BI. O Lumo traduz a fórmula para o mesmo SQL das outras expressões, e por isso a liberação do usuário, o filtro do Datamart e a restrição por usuário continuam valendo, inclusive com ALL. O DAX vale só para medidas, e é um recurso em beta.

As fórmulas por tarefa, o que não é suportado e as diferenças em relação ao Power BI estão na Referência rápida DAX. O passo a passo, em 12 lições, está no Tutorial DAX.

Limites

BETWEEN precisa de dois valores; com um só, a consulta falha.
Dentro de ${...} não pode haver }: a primeira chave fechada encerra a expressão.
Nos filtros de ${...}, só =, IN, LIKE, BETWEEN, > e < (veja acima).
use() aceita só coluna física e só no começo da fórmula.
As funções de data só valem dentro de ${...} e <...>; fora delas, use as funções de data do próprio SQL.

Pelo Lumo CLI

As expressões da Aplicação também podem ser criadas e versionadas em YAML. Veja a referência de app.

Linguagem de expressões ​

Referências ​

use(): data alternativa ​

${...}: calcular com outro filtro ​

Ler o filtro da tela com <...> ​

Exemplos ​

Funções de data ​

Medidas em DAX ​

Limites ​