When you divide two whole numbers (e.g. two counts) in Postgres or Redshift, the value returned will be an integer value, ignoring any decimals. For example, 3 / 4 will return 0.
This can affect metrics and table calculations you define in Lightdash.
For example, if you're using Redshift or Postgres and you define metrics like this:
models:
- name: orders
meta:
metrics:
order_completion_rate:
type: number
sql: ${total_completed_orders} / ${total_orders}
round: 2 # this should return 2 decimal places
percent_orders_with_voucher:
type: number
sql: 100 * ${total_orders_with_voucher} / ${total_orders}
round: 2
Then your results could have Order completion rate with a value of 0 and Percent orders with voucher with empty decimal values:
Date Month | Total completed orders | Total orders with voucher | Total orders | Order completion rate | Percent orders with voucher |
2024-03 | 3 | 6 | 9 | 0.00 | 66.00 |
These results are incorrect.
To fix this, you need to multiply your numerator by 1.00 in the metric definition.
If you multiply your numerator by a value with decimals (e.g. 1.00), then the values will be cast as floats and your results will return decimals, like you'd expect.
So, you'd change your metric definitions to something like this:
models:
- name: orders
meta:
metrics:
order_completion_rate:
type: number
sql: 1.00 * ${total_completed_orders} / ${total_orders}
round: 2
percent_orders_with_voucher:
type: number
sql: 100.00 * ${total_orders_with_voucher} / ${total_orders}
round: 2
Then your results will correctly show decimal values, like this:
Date Month | Total completed orders | Total orders with voucher | Total orders | Order completion rate | Percent orders with voucher |
2024-03 | 3 | 6 | 9 | 0.33 | 66.66 |