Question

# problem of calculating measures in foreign views

• 2 replies
• 23 views

• Member
• 28 replies
`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

Userlevel 6

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

@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 ;;  }``