Conditions from joined tables and measures fail sometimes

When I add a filter, one of my measure is breaking wit an error that I don’t understand. Here’s the setup:

We’re reporting on responses to a Net Promoter Score (NPS) quesion on a customer satisfaction survey. We have several clients who get a “client=x” permission on the dashboard when it’s embeded in our app. This approach generally works, but I have one measure that is not cooprating when I use that upstream filter (it’s 2 joins away…)

simplified relationship model in the dataset:

Model Defintions
Answers to NPS Questions, the column fact_answer_nps.nps_contribution_hidden is a dimension added in a thequery model as follows:

    when q.answer::int between 9 and 10 then 'promoter'
    when q.answer::int between 7 and 8 then 'neutral'
    else 'detractor'
  end as nps_label,
    when q.answer::int between 9 and 10 then 1
    when q.answer::int between 7 and 8 then 0
    else -1
  end as nps_contribution_hidden
  {{ #clarus_c_survey_question_answer q }}
  join {{ #clarus_dim_survey_questions t }} on t.question = q.question
  t.question_type = 'nps'

fact_answer_nps is one of several fact tables that is extracted from clarus_c_survey_question_answer In this case, I’m casting the answer to an integer for this question type. the column answer in clarus_c_survey_question_answer is a text or bool for other questions types in other fact tables.

This model also includes these measure (in AML):

    label: "Count"
    type: "number"
    definition: @sql {{ r_id }};;
    description: "Count of NPS answer"
    aggregation_type: "count"

  measure nps_contribution {
    label: "NPS Contribution"
    type: "number"
    definition: @sql {{ nps_contribution_hidden }};;
    aggregation_type: "sum"
    format: "#,###"

  measure nps_score {
    label: "NPS Score"
    type: "number"
    definition: @sql {{ nps_contribution }}::float / {{ count }} * 100;;
    description: "Calculated NPS score"
    aggregation_type: "custom"
    format: "#,###0.00"

Here’s how the problem materializes…

Create a report that that joins to the client table and and includes ‘nps_contribution_hidden’ then, I get the error

ERROR: invalid input syntax for type integer: ""

screen shots of interest:

Hi Matt Stone,

Thanks for reaching out to us!

Regarding the issues, I think the dimension NPS Contribution for measure contains “” (empty string) and you are trying to cast it into number which can cause errors.

For better investigation, it could be very useful if you give us permission to impersonate so that we can figure out what causes this bug.

Looking forward to hearing from you soon.


looking for an empty string was a good idea, so I tried this:

select answer, count(*), sum(answer :: int)
  {{ #clarus_c_survey_question_answer q }}
  --{{ #fact_answer_nps q }}
where question ~ 'nps'
group by 1 order by 1

the where clause of question ~ 'nps' is the logic that collects the underlying data for the fact table.
I tried the same query on clarus_c_survey_question_answer (contains answers to all question types) and fact_answer_nps wherein all answers are cast to integers. In both cases the query worked, including the sum aggregation.

the ERROR seems to be related w/ the generated sql (or the way I built up the models in AML) b/c that is a Postgress error that we’re getting.

I can’t share access b/c this is a production system, but I could share the AML. via github.