6 Replies Latest reply on Jan 28, 2014 9:47 AM by Craig Mullins

# I need a calculated field to annualize data based on frequency

i need a calculated field to annualize each row based on the dimension "Freq".

for example:

• weekly would be 16648 x 52 weeks per year = 865696
• bi-weekly would be 2963 x 26 bi-weekly periods per year = 77038
• etc

it should be a simple formula but i cannot seem to get the formula below to work.

IF [Freq]="Weekly" then [Amount] *52 else

IF [Freq]="Bi-Weekly" then [Amount] *26 else

IF [Freq]="Monthly" then [Amount] *12 else

IF [Freq]="Quarterly" then [Amount] *4 else

IF [Freq]="Annually" then [Amount] *1 else

end

can anyone tell me where i am off?

thanks

• ###### 1. Re: I need a calculated field to annualize data based on frequency

I think this formula will work:

```IF     [Freq] = "Weekly"    then [Amount] * 52
ELSEIF [Freq] = "Bi-Weekly" then [Amount] * 26
ELSEIF [Freq] = "Monthly"   then [Amount] * 12
ELSEIF [Freq] = "Quarterly" then [Amount] * 4
ELSEIF [Freq] = "Annually"  then [Amount] * 1
END
```

Workbook Version:  8.1

1 of 1 people found this helpful
• ###### 2. Re: I need a calculated field to annualize data based on frequency

your formula calculation is valid but it will not display the results??? it must a setting issue? i went thru your twbx but cannot see the error

• ###### 3. Re: Re: I need a calculated field to annualize data based on frequency

Is there any chance you could share a packaged workbook with dummy data?

It is much easier for helpers when we can test what we are doing with the same data the questioner has difficulties with.

Before doing that, you might try this formula and see if it makes any difference:

```IF     ATTR([Freq]) = "Weekly"    then SUM([Amount]) * 52
ELSEIF ATTR([Freq]) = "Bi-Weekly" then SUM([Amount]) * 26
ELSEIF ATTR([Freq]) = "Monthly"   then SUM([Amount]) * 12
ELSEIF ATTR([Freq]) = "Quarterly" then SUM([Amount]) * 4
ELSEIF ATTR([Freq]) = "Annually"  then SUM([Amount]) * 1
END

```
• ###### 4. Re: Re: Re: I need a calculated field to annualize data based on frequency

i tried the alternative formula - it did not help.

dummy data twbx with problem displaying calc field attached

• ###### 5. Re: Re: Re: Re: I need a calculated field to annualize data based on frequency

The reason it didn't work was because the formula referred to the aliases rather than the real data.

This works:

```IF     [Freq] = "W"  then [Amount] * 52
ELSEIF [Freq] = "BW" then [Amount] * 26
ELSEIF [Freq] = "M"  then [Amount] * 12
ELSEIF [Freq] = "Q"  then [Amount] * 4
ELSEIF [Freq] = "A"  then [Amount] * 1
END

```

.

See more in attached workbook.

1 of 1 people found this helpful
• ###### 6. Re: Re: I need a calculated field to annualize data based on frequency

major oversight on my part - the aliases were done so long ago i forgot about them.

Thanks a million !!