Question

problem of calculating measures in foreign views

  • 18 February 2021
  • 2 replies
  • 23 views

Hello, I would like to calculate the total of a measure (sum_dig_total_ht) in a foreign table but the displayed result is not correct. The value is high. how could i correctI give you my model and my views, so that you give me your point of view on my model, if it is optimal or if my joints are well done. Thanks
explore: derived_table_sales {
join: store {
type: left_outer
relationship: many_to_one
sql_on: ${derived_table_sales.id_store}=${store.id_store } ;;
}
join: derived_table_digital_sales {
type: left_outer
relationship: one_to_one
sql_on: ${derived_table_sales.id_store}=${derived_table_digital_sales.id_store} ;;
}
}


view: derived_table_digital_sales {
derived_table: {
sql: select
m.id_store,
m.surface,
sum(d.total_ht)
from tab.dig_sales as d
left join tab.store as m
on m.cd_store = d.code_store
group by 1,2
;;
}
}

view: derived_table_sales {
derived_table: {
sql: select
id_sales,
id_store,
date,
sum(total_ht)
from tab.sales
group by 1,2,3
;;
}

measure: sum_dig_total_ht {
type: sum
value_format_name: eur
sql: ${derived_table_digital_sales.total_ht} ;;
}
}

 


2 replies

@Dawid_Nawrot  I want to calculate my measurements according to the user's choice of the value which is the date field in the view derived_table_sales

 

 measure: sum_dig_total_ht {
label: "n"
type: sum
value_format_name: decimal_0
sql: CASE
WHEN {% condition date_filter %} CAST(${date_sales_date} AS TIMESTAMP) {% endcondition %}
THEN ${derived_table_digital_sales.total_ht}
END ;;
}

measure: sum_dig_total_ht_ly {
label: "n-1"
type: sum
value_format_name: decimal_0
sql: CASE
WHEN {% condition date_filter %} CAST(DATE_ADD(DATE(${date_sales_date}), INTERVAL 1 YEAR) AS TIMESTAMP) {% endcondition %}
THEN ${derived_table_digital_sales.total_ht}
END ;;
}

 

Userlevel 6
Badge

Why are you not creating the sum measure in its view? The derived_table_digital_sales?


What is the current problem? Any errors? Show us some data and what’s not right

Reply