Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-06-2026 11:44 AM - edited 05-06-2026 11:46 AM
Hi Steve, I have a similar problem , I am trying to translate the following query into yaml
SELECT
-- ATTRIBUTES
FechaReporte_Crm,
zonaventa_CRM,
ClaseOperacionComercialId_Crm,
--BusinessPartner_Crm,
-- MEASURES
MEASURE(ContadorReporte_Crm) AS ContadorReporte_Crm,
MEASURE(Efectividad_CRM) AS Efectividad_CRM,
-- % DISTRIBUTION
MEASURE(ContadorReporte_Crm) * 100.0
/ SUM(MEASURE(ContadorReporte_Crm)) OVER (
PARTITION BY
---ATTRIBUTES IN SELECT LIST
FechaReporte_Crm
, zonaventa_CRM
-- , ClaseOperacionComercialId_Crm
) AS Pct_Distribucion_Abs_Crm,
---ATTRIBUTES IN SELECT LIST
MEASURE(Efectividad_CRM) * 100.0
/ SUM(MEASURE(Efectividad_CRM)) OVER (
PARTITION BY FechaReporte_Crm
, zonaventa_CRM
--, ClaseOperacionComercialId_Crm
) AS Pct_Distribucion_Efectividad_Crm
FROM datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx
WHERE 1=1
AND zonaventa_CRM IS NOT NULL
AND FechaReporte_Crm = '20260505'
GROUP BY ALL
ORDER BY 2;
Here is how my yaml looks like:
The tricky part here is that I am not able to dinamycally transalate the dimensions that participate in the PARTITION BY clause so I need to do it at the metric view level.
Here is how my yaml looks like:
---FIX PROD CATALOG FROM PRD2 TO PRD
CREATE OR REPLACE VIEW datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx
WITH METRICS
LANGUAGE YAML
AS
$$
version: 1.1
source: datalakekof_dev.comercial_vista.com_vw_crm_efectividad_mx
dimensions:
- name: ReporteId_Crm
expr: ReporteId_Crm
- name: Responsable_Crm
expr: Responsable_Crm
- name: BusinessPartner_Crm
expr: BusinessPartner_Crm
- name: ClienteTexto_Crm
expr: ClienteTexto_Crm
- name: FlagCancelacion_Crm
expr: FlagCancelacion_Crm
- name: MedioIngreso_Crm
expr: MedioIngreso_Crm
- name: Prioridad_Crm
expr: Prioridad_Crm
- name: Canal_Crm
expr: Canal_Crm
- name: Encuestador_Crm
expr: Encuestador_Crm
- name: CodigoProducto_Crm
expr: CodigoProducto_Crm
- name: FechaReporte_Crm
expr: FechaReporte_Crm
- name: RutaPreventa_Crm
expr: RutaPreventa_Crm
- name: NivelMercadeo_Crm
expr: NivelMercadeo_Crm
- name: UnidadOperativaId_Crm
expr: UnidadOperativaId_Crm
- name: UnidadOperativaId1_Crm
expr: UnidadOperativaId1_Crm
- name: AgenteOperadorId_Crm
expr: AgenteOperadorId_Crm
- name: AgenteOperadorTexto_Crm
expr: AgenteOperadorTexto_Crm
- name: NumeroParte_Crm
expr: NumeroParte_Crm
- name: EstadoReporteTexto_Crm
expr: EstadoReporteTexto_Crm
- name: Dia
expr: Dia
- name: Mes
expr: Mes
- name: Filtro2_Crm
expr: Filtro2_Crm
- name: Filtro1_Crm
expr: Filtro1_Crm
- name: PaisAbv_Crm
expr: PaisAbv_Crm
- name: GrupoVendedor_CRM
expr: GrupoVendedor_CRM
- name: GrupoOficina_CRM
expr: GrupoOficina_CRM
- name: DistribuidoraRepartoTexto_ECC
expr: DistribuidoraRepartoTexto_ECC
- name: RutaReparto_CRM
expr: RutaReparto_CRM
- name: ZonaVenta_CRM
expr: ZonaVenta_CRM
- name: ZonaVentaTexto_Crm
expr: ZonaVentaTexto_Crm
- name: ModoVenta_Crm
expr: ModoVenta_Crm
- name: AreaId_Crm
expr: AreaId_Crm
- name: MotivoId_Crm
expr: MotivoId_Crm
- name: MotivoTipoTexto_Crm
expr: MotivoTipoTexto_Crm
- name: MotivoCodigoId_Crm
expr: MotivoCodigoId_Crm
- name: MotivoTexto_Crm
expr: MotivoTexto_Crm
- name: CodigoGrupoTexto_Crm
expr: CodigoGrupoTexto_Crm
- name: MotivoGrupoId_Crm
expr: MotivoGrupoId_Crm
- name: ClaseOperacionComercialId_Crm
expr: ClaseOperacionComercialId_Crm
- name: ClaseOperacionComercialTexto_Crm
expr: ClaseOperacionComercialTexto_Crm
- name: ClaseOperacionComercialTextoLargo_Crm
expr: ClaseOperacionComercialTextoLargo_Crm
measures:
- name: ContadorReporte_Crm
expr: SUM(ContadorReporte_Crm)
- name: ContadorReporteResolucion_Crm
expr: SUM(ContadorReporteResolucion_Crm) * 100
- name: Efectividad_Crm
expr: COALESCE(TRY_DIVIDE(ContadorReporteResolucion_Crm,ContadorReporte_Crm),0)
$$
;
The tricky part here is that I am not able to dinamycally transalate the dimensions that participate in the PARTITION BY clause so I need to do it at the metric view level.
Also notice level of detail in PARTITION BY contains minus 1 column than in SELECT columns list, so when adding an extra column to SELECT, we need to have the same columns in PARTITION BY minus the one lastly added.
Do you have any idea how to achieve this behavior?
Thanks in advance
