Grouping multiple lines by date or summing column

[robertr1]

Hello all,

Struggling here with Looker and trying to combine multiple rows. The data I’m working with is survey-scores by date. What I’m getting is something like this:

Date        Knowledge      Friendly        Caring

11/6        4                        3                    4

11/6        4                        1                    1

11/6        3                        3                    3

11/5        1                        1                    1

These are all dimensions by the way. I’m told they are split on different lines even though they are all the same date because the combination of scores is unique.

What I’d like is to group them and have a sum instead with one line per date:

Date        Knowledge      Friendly        Caring

11/6        11                      7                    8

11/5        1                        1                    1

Thanks for reading.

[leobardor]

Hi Robertr,

One way to get the solution is by creating new measures with the “sum” function in your LooKML:

(Based on your information sample):

Datasource original:

# The name of this view in Looker is "Surveys Scores"
view: surveys_scores {

  sql_table_name: tomy.surveys_scores ;;



  dimension: caring {
    type: number
    sql: ${TABLE}."caring" ;;
  }




  dimension_group: date_survey {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}."date_survey" ;;
  }

  dimension: friendly {
    type: number
    sql: ${TABLE}."friendly" ;;
  }

  dimension: knowledge {
    type: number
    sql: ${TABLE}."knowledge" ;;
  }

  measure: count {
    type: count
    drill_fields: []
  }

  measure: total_knowledge {
    type: sum
    sql: ${knowledge} ;;
  }

  measure: total_friendly {
    type: sum
    sql: ${friendly} ;;
  }
  measure: total_caring {
    type: sum
    sql: ${caring} ;;
  }


}

(LookML, View file).

Ensure the explore exists in your Looker - Model file, if not, create the explore:

explore : surveys_scores {}

Save your changes.

(It is important to save your changes ,  please take a look at Looker - Git control)

Now you will be able to navigate in the explore that was just created:

Now, in the field picker the new measures should be available to use, select the indicated fields and click on “run” button:

Regards,

Leo

[robertr1]

Thank you for this incredibly detailed response Leo. Unfortunately I immediately hit a road-block as I do not have permissions to edit in LookML.