8 Replies Latest reply on Jul 19, 2018 6:51 AM by Ana Ana

# Errors in calculated fields

Hello everybody, I have a problem with a formula that I am working on.

The total budget of a business is provided at the beginning of the month and the sales are at calendar day level. I wish to see in a new column, the sales that were made + how much should the business sale in the remaining days of the month in order to reach the target. The sum of every line should give the original budget.

I tried this formula but it is not working.

if [Calendar day]<TODAY() THEN ([Sales])

ELSE     (IF RUNNING_SUM(SUM([Budget]))>RUNNING_SUM(SUM([Sales]))

THEN (RUNNING_SUM(SUM([Budget]))-RUNNING_SUM(SUM([Sales])))/5  END) END

For now I tried not to complicate the formula with the count of number of days remaining, and I just put it manually. I also attached a quick calculation in excel to show what I expect from the calculated field.

Here is the link to the workbook: https://public.tableau.com/views/Case2file/Sheet1?:embed=y&:display_count=yes&publish=yes

• ###### 1. Re: Errors in calculated fields

Try this:

if ATTR([Calendar day])<TODAY() THEN SUM([Sales])

ELSE     (IF RUNNING_SUM(SUM([Budget]))>RUNNING_SUM(SUM([Sales]))

THEN (RUNNING_SUM(SUM([Budget]))-RUNNING_SUM(SUM([Sales])))/5  END) END

1 of 1 people found this helpful
• ###### 2. Re: Errors in calculated fields

Hi,

I works very well,  but I also need the total which is incorrect.

https://public.tableau.com/views/Case2file/Sheet1?:embed=y&:display_count=yes

Can I calculate the total without using analysis tab?

• ###### 3. Re: Errors in calculated fields

HI Ana

I ma not sure if this really works as common method in actual data, anyways based on your logic, is this what you want?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Errors in calculated fields

Unfortunately the total is still the sum of sales and does not include the remaining budget.

I need the total to be correct in order to be able to drill up and down. If i want to see for 2018, the number is not correct.

• ###### 5. Re: Errors in calculated fields

HI Ana,

Hope this helps.

first()=last() specifies the G.total cell.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: Errors in calculated fields

Thank you so much for the help. It solves partially my problem.

On an yearly, monthly and daily view everything is fine, but in the weekly  part it does not calculate correctly. It does not take into account the budgeted sales after the 25th of June (the sum of the 3 weeks does not equal to 8'000)

• ###### 7. Re: Errors in calculated fields

If you plan to use the hierarchy for the calendar date, then it may not be possible to achieve what you need. Please note that the formula uses calendar date and not the week of the calendar date.

I would suggest that you have a parameter by which you decide what level of information you want to see.

Here is the output based on the above suggestion.

To achieve the above results, I have used a parameter and 3 calculated fields as shown below:

Hope this helps. Updated workbook attached.

1 of 1 people found this helpful
• ###### 8. Re: Errors in calculated fields

Thank you so much for your help.