problem of calculating measures in foreign views

[tatuspark]

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

[Dawid]

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

[tatuspark]

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