11 Replies Latest reply on Feb 28, 2017 1:21 PM by Chris McClellan

Calculation Error

Hello,

I am trying to determine accuracy for forecast versus actuals. I have created a calculation to seperate the Actuals and Forecast, but now when I try to divide forecast by actuals, it keeps returning null. I have attached a sample workbook.

Thank you in advance,

Bianca

• 1. Re: Calculation Error

Bianca,

See Screenshot and Calculation

Thanks

Deepak

• 2. Re: Calculation Error

Hi Deepak,

Thank you for your response. However, when I try that it still shows null in the Accuracy Column when I look at the full data. Could this be because the Pivot Table is breaking the items onto seperate lines? I have attached a screenshot. The items highlighted should be one line with Forecast of 1,000; Actuals of 1,000 and Accuracy of 100%. It appears that the error is coming from the Pivot.

Thank you,

Bianca

• 3. Re: Calculation Error

Can you share the above sheet in tableau workbook here to see whats going on?

• 4. Re: Calculation Error

Hi Deepack,

For some reason when I reply to a question, it does not let me attach any documents. The screenshot I sent is based on the Data View in Tableau. And below is the data I am pulling into Tableau from Excel. I took Jan-2016_F, Jan-2016_A, Feb-2016_F, Feb-2016_A and pivoted the coloumns together. I then split the pivot fields to create Category, using Aliases "A"= Actuals and "F"= Forecast and created a date field with Month and Year.

Using Project # C161002 as an example, I see I have a Jan Forecast of \$1,000 with \$0 in Actuals this should be an Accuracy of 0% then in February I have a Forecast of \$1,000 and Actuals of \$1,000 this should have an Accuracy of 100% making my Average Accuracy for Project C161002 50%.

Tableau however is doing this:

 Project  Number PM Lead Funding Type Jan-2016_F JAN-2016_A FEB-2016_F FEB-2016_A C161020 Admin O&M \$               44,396 \$                         - \$               44,396 \$                        30 C161002 Admin O&M \$                 1,000 \$                         - \$                 1,000 \$                  1,000 C161070 Service Centers Capital \$                 1,600 \$                  3,710 \$                 1,600 \$                  4,445 C161070 Service Centers O&M \$                 1,600 \$                  2,630 \$                 1,600 \$                  1,120 C161100 Service Centers Capital \$                 7,982 \$                        60 \$                 7,982 \$                     176 C161110 Service Centers Capital \$               14,400 \$                13,789 \$               14,400 \$                  4,898 C161300 Corporate Offices O&M \$                 8,964 \$                16,181 \$                 8,964 \$                  4,066 C161530 Service Centers O&M \$                 1,600 \$                10,571 \$                 1,600 \$                  2,809 C161530 Service Centers Capital \$                 1,601 \$                11,661 \$                 1,601 \$                  3,606 C161570 Majors Capital \$                 5,000 \$                      504 \$                 5,000 \$               19,160 C161610 Service Centers O&M \$                     500 \$                         - \$                     500 \$                         - C161680 Service Centers O&M \$                 1,000 \$                         - \$                 1,000 \$                         - C161750 Service Centers Capital \$                 5,000 \$                         - \$                 5,000 \$                         - C161760 Majors O&M \$                 1,500 \$                         - \$                 1,500 \$                         - C161770 Majors Capital \$               25,000 \$                17,417 \$               25,000 \$                  1,062 C161780 Service Centers BU Funded \$               82,573 \$                         - \$               82,573 \$                         - C161790 Service Centers BU Funded \$               25,000 \$                         - \$               25,000 \$                         - C161800 Majors Capital \$                 5,000 \$                  1,109 \$                 5,000 \$                     617 C161890 Majors Capital \$                 5,000 \$                      263 \$                 5,000 \$               17,166 C161900 Majors Capital \$                 5,000 \$                  1,159 \$                 5,000 \$                  6,300 C161910 Service Centers Capital \$                     500 \$                         - \$                     500 \$                         - C162000 Corporate Offices Capital \$                 1,000 \$                         - \$                 1,000 \$                         - C162010 Corporate Offices O&M \$               25,212 \$                  6,789 \$               25,212 \$                  4,588 C162010 Corporate Offices Capital \$            125,000 \$              160,928 \$            125,000 \$                  4,898 C162040 Corporate Offices Capital \$                 2,000 \$                         - \$                 2,000 \$                  1,959 C162060 Corporate Offices O&M \$               29,982 \$                  7,179 \$               29,982 \$                  1,959 C162060 Corporate Offices Capital \$                 1,200 \$                28,318 \$                 1,200 \$                  4,634 C162090 Corporate Offices O&M \$               16,435 \$                16,435 \$               16,435 \$                  1,469 C162100 Majors Capital \$                 5,000 \$                      120 \$                 5,000 \$                  5,267 C162200 Service Centers BU Funded \$               71,589 \$                         - \$               71,589 \$                         - C162200 Service Centers BU Funded \$               10,471 \$                         - \$               10,471 \$                         - C162260 Corporate Offices Capital \$               10,000 \$                      667 \$               10,000 \$                  9,719 C162280 Corporate Offices Capital \$               21,015 \$                  1,985 \$               21,015 \$               15,422 C162300 Service Centers Capital \$                 1,000 \$                         - \$                 1,000 \$                         - C162320 Service Centers Capital \$                     500 \$                         - \$                     500 \$                         - C162330 Service Centers Capital \$                 1,000 \$                         - \$                 1,000 \$                         - C162380 Service Centers Capital \$                 2,500 \$                         - \$                 2,500 \$                         - C162390 Service Centers O&M \$                 1,000 \$                  1,300 \$                 1,000 \$                     980 C162400 Service Centers BU Funded \$               16,250 \$                         - \$               16,250 \$                         - C162400 Service Centers BU Funded \$                 8,750 \$                         - \$                 8,750 \$                         - C162480 Service Centers Capital \$                 1,000 \$                         - \$                 1,000 \$                         - C162500 Majors Capital \$               13,171 \$                  2,329 \$               13,171 \$               17,578 C162520 Majors Capital \$                 8,220 \$                  2,780 \$                 8,220 \$                  2,408 C162530 Service Centers Capital \$               48,500 \$                48,500 \$               48,500 \$                         - C162540 Majors Capital \$               10,000 \$                      152 \$               10,000 \$                  6,243 C162550 Service Centers Capital \$                 1,000 \$                         - \$                 1,000 \$                         - C162560 Service Centers Capital \$                 1,000 \$                         - \$                 1,000 \$                     980 C162570 Majors Capital \$               10,000 \$                  1,105 \$               10,000 \$                  2,328 C162600 Service Centers Capital \$                 1,000 \$                         - \$                 1,000 \$                         - C162610 Majors Capital \$                 9,324 \$                  3,676 \$                 9,324 \$                  2,657 C162630 Majors Capital \$                 5,000 \$                  1,830 \$                 5,000 \$                  2,088 C162670 Service Centers Capital \$                     500 \$                         - \$                     500 \$                         - C162700 Service Centers BU Funded \$               10,000 \$                         - \$               10,000 \$                         - C162700 Service Centers BU Funded \$                 2,500 \$                         - \$                 2,500 \$                         - C162730 Service Centers Capital \$                 1,000 \$                         - \$                 1,000 \$                         - C162740 Service Centers Capital \$                 1,000 \$                         - \$                 1,000 \$                         -
• 5. Re: Calculation Error

Bianca Reddick wrote:

Hi Deepack,

For some reason when I reply to a question, it does not let me attach any documents.

Click on the "Advanced editor and add an attachment" link in the top right of the reply window

Because you've added the Category dimension, you don't have a row that includes Actual & Forecast data on the same row.

I think you'll need an LOD (Level Of Detail) calc that excludes Category in order for it to work as you require.  Even then you'll get a repetition of the Accuracy value, so maybe try removing the Category from the viz and see how that works.

Please let me know how you go

• 6. Re: Calculation Error

Hi,

Did u tried the following?

IF sum([Forecast])>sum([Actuals])

THEN sum([Actuals])/sum([Forecast])

ELSE sum([Actuals])/sum([Forecast])

END

• 7. Re: Calculation Error

Is that a copy/paste error Satish Katta ? the THEN and the ELSE are the same.

• 8. Re: Calculation Error

SUM() is the key

• 9. Re: Calculation Error

But the THEN and the ELSE are the same, that's a maintenance nightmare

• 10. Re: Calculation Error

Hi Chris,

I don't think IF statement is required here( except check for NULL in Actual or Forecast). I am new to Tableau and just joined the community. Thought i could help fellow user. Any way i got some values instead of NULLS as explained in the original post. Let  me now your thoughts.

• 11. Re: Calculation Error

Helping is great, and always welcomed    What I mean is the code you posted :

IF sum([Forecast])>sum([Actuals])

THEN sum([Actuals])/sum([Forecast])

ELSE sum([Actuals])/sum([Forecast])

END

The THEN and ELSE are exactly the same, so the IF is not needed at all.  With the data that Bianca Reddick showed, as soon as she adds the Category dimension then it won't work any more (Actuals and Forecast are not on the same row)