Wrong calculation?

Question & Answer
,
1

Hi everyone,

Unfortunately the calculated field that I create brings me “wrong” answers and trying to understand why.
Please see an excel calculation where I sum the amount of days in downtime (127). I then divide it by the number of rows in the column A (168) which is approx. 0.75.
image
However, when creating a calculated field:

image
image780×275 8.31 KB

It gives me different values (I know the viz is on a date level but still something looks weird…
image
image998×492 38.7 KB

Any ideas why this happens?

2

Hi @Etai ,

I can see a mismatch in the excel snip with the explanation mentioned above. The sum formula is referencing row number 124 through 169. However you are dividing by 168. So 0.75 may not be the expected result.

Further I have 2 suggestions here that can help you debug this issue.

  • Breakdown the formula to create sum of days as one formula and another formula for number of id. Check if these values match with what you are calculating in excel.
  • The first calculation does not have date in the fields, however the visual snip has a field called started month. For the comparison, try removing and seeing the value that comes in total.

Hope this helps!

Thanks,
Prantika

3

Thanks Pratinka,

You are right that it is counting only rows 124 onwards but I thought it does not matter as I am trying to sum a numb. Should’t it treat the NULL as 0? or maybe that is the problem.

Anyway, I will try your suggestion as well

Thanks,

Etai

4

Hi @Etai, NULL is not treated as 0, try nullIf(fieldname,0) - nullIf - Amazon QuickSight

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena

5

Hi @Etai,
It’s been awhile since we last heard from you. Did you have any additional questions regarding your initial topic or did the solutions above provide you with an answer?

If we do not hear back from you within 3 business days, I’ll go ahead and mark the solution.

Thank you!