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:
select
q.client,
q.r_id,
q.survey_type,
q.created,
q.question,
q.answer::int,
case
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,
case
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
from
{{ #clarus_c_survey_question_answer q }}
join {{ #clarus_dim_survey_questions t }} on t.question = q.question
where
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:
