Calculation on one field with different values

I need to sum values coming from a field e.g. one field which is labelled ‘Score H’.
There are different questions that relates to this score H. The goal is to sum the values answered on those different questions related to score H.
And I need to sum those values depending on the value recorded. e.g : i need to a sum for questions answered with value 4 and another sum for questions answered with value 3. And one other sum for value 2. So that i can view the different level of quality for the score H on different questions.
For this i try to create a formula to sum when a value of 4 is recorded as displayed here :
sum(${input_fields.field_value_number} where ((${input_fields.field_label}=“Score H”)AND (${input_fields.field_value_number}=4)))
but the calculation leads to an error. Any help would be appreciated !

0 4 122

4 REPLIES 4

Hello @vincentl and welcome!

Are you trying to do that in SQL/LookML or using the Table Calculation at the Explore level?

Hello!

Thanks ! I am using the Table Calculation at the Explore level.

Le lun. 20 juil. 2020 à 17:16, Cyril Marques via Looker Community support@looker.com a écrit :

the where syntax isn’t used in table calcs. You will probably need to do some nested if statement:

if(${input_fields.field_label}=“Score H” and ${input_fields.field_value_number}=4, sum(${input_fields.field_value_number}), 
   if(${input_fields.field_label}=“Score H” and ${input_fields.field_value_number}=3, sum(${input_fields.field_value_number_2}), 
      if(${input_fields.field_label}=“Score H” and ${input_fields.field_value_number}=2, sum(${input_fields.field_value_number_3}), 0
      )
   )
)

Hi there,
thanks for the answer. I am still an error though : see screenshot. I cannot know if it because of the first “Score H” string or the = sign (on line 2, before the “4”, which is a digit)

never-displayed