Ifelse divided by avgOver in calculated field

Question & Answer
,
1

I am trying to translate this SQL into a calculated field

select year, month, sum(amortized_cost) as cost, 
sum(amortized_cost) /
(case 
    when month = '2' then 28
    when month = '4' then 30
    when month = '6' then 30
    when month = '9' then 30
    when month = '11' then 30
    else 31
end) * 30 as normalised_cost
from tmp_summary
group by year, month

This calculation succeeds

avgOver(sum({amortized_cost}),[{year},{month}]) / 30

but this one fails with a generic error

avgOver(sum({amortized_cost}),[{year},{month}]) / ifelse({month} = '2', 28,31)
2

It’s duplicating code but I think this will work.

ifelse({month} = '2', avgOver(sum({amortized_cost}),[{year},{month}]) /  28, avgOver(sum({amortized_cost}),[{year},{month}]) / 31)
1 Like
3

It’s complaining about that too :frowning:

Screenshot 2023-05-18 at 1.58.31 pm
Screenshot 2023-05-18 at 1.58.31 pm1018×91 12.9 KB

4

Hi @TheOfficialYakultMas

Is this still an issue?

5

I ended up having to make one days_per_month column then going {cost}/{days_per_month}*30

1 Like
6

Thank you for letting the community know your solution, @TheOfficialYakultMas! :slight_smile:

1 Like