Solved

Getting Error in creating new dimension and measure.

  • 7 April 2022
  • 2 replies
  • 28 views

 WITH last_run AS
        (
          SELECT
            max(date_run) as last_run_date
          FROM
            analytics_s.firm_stats
        ),
        Transaction_sum AS (
        SELECT sum(Transactions) as TransactionMonthly,
        month(date_run) as Month,
        year(date_run) as Year
          from analytics_s.firm_stats
          group by 2,3

        )
      SELECT
        fs.*,t.TransactionMonthly,t.Month || t.Year as distinct_key
      FROM
        analytics_s.firm_stats fs
       LEFT OUTER JOIN
        last_run
      left outer join
        Transaction_sum t on t.month = month(fs.date_run) and t.year = year(fs.date_run)

The dimension and measure in which I am getting error :

  dimension: TransactionMonthly {
    type: number
    sql: ${TABLE}."TransactionMonthly";;
  }
  
  measure: Transactions {
    type : sum_distinct
    sql_distinct_key: ${TABLE}."distinct_key" ;;
    sql: ${TABLE}."TransactionMonthly";;
    label : "$ Transaction"
  }

icon

Best answer by Dawid 7 April 2022, 09:55

View original

This topic has been closed for comments

2 replies

Userlevel 7
Badge +1

Remove the quotes from the sql parameter and give it a try:


    sql: ${TABLE}.TransactionMonthly;;

Thanks @Dawid . It helped now I am getting result without any error. You rock