avg score with null values

Follow

sivan Zohar Avda

March 15, 2018 08:59

Hii,

 

I have a problem when I calculate the avg score between 2 survey for each dimension and on of them has null values the avg is wrong.

 

For example :

 

dim	Score survey   type 1	Score survey type 2	avg score total
X1	0.97	0.80	0.89
X2	0.93	1.00	0.96
X3	1.00	null	0.50
X4	0.70	0.25	0.48
X5	0.67	null	0.33
X6	0.97	1.00	0.99

 

 

The avg is 0.5*type +0.5*type 2

 

on X3 the answer need to be 1 and not 0.5.

 

what can I do to solve it in the dashboard preferably without changing the model.

 

 

Thanks

 

Sivan

-1

• 

  Andrew Block March 22, 2018 14:40

  Sivan,

  Here is a workaround let me know if this works for you

  Create an Average Score (AVG) field with the following CASE statement:

  

  Which would produce this:

  

  0

  Permalink
• 

  Sisense Dev May 02, 2018 13:18

  Hi Andrew,

  This doesn't work if the total score fields have no records as opposed to records with a blank. (hope that makes sense)
  Can you think of a way to capture missing values and do the same as you suggest for nulls? (or fill with 0)
  
  In my case there are millions of records so I'm reluctant to add dummy rows

  Thanks

  Jason

   

  0

  Permalink

Please sign in to leave a comment.