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

Calculation Error

Bianca Reddick Feb 28, 2017 11:17 AM

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

Test 2.twbx 34.9 KB

1. Re: Calculation Error

Deepak Rai Feb 28, 2017 11:31 AM (in response to Bianca Reddick)

Bianca,
See Screenshot and Calculation
Thanks
Deepak
Like Show 0 Likes(0)

Actions
2. Re: Calculation Error

Bianca Reddick Feb 28, 2017 12:13 PM (in response to Deepak Rai)

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
Like Show 0 Likes(0)

Actions
3. Re: Calculation Error

Deepak Rai Feb 28, 2017 12:17 PM (in response to Bianca Reddick)

Can you share the above sheet in tableau workbook here to see whats going on?
Like Show 0 Likes(0)

Actions

4. Re: Calculation Error

Bianca Reddick Feb 28, 2017 12:39 PM (in response to Deepak Rai)

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

Chris McClellan Feb 28, 2017 12:47 PM (in response to Bianca Reddick)

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
Like Show 0 Likes(0)

Actions
6. Re: Calculation Error

Satish Katta Feb 28, 2017 12:59 PM (in response to Bianca Reddick)

Hi,

Did u tried the following?

IF sum([Forecast])>sum([Actuals])
THEN sum([Actuals])/sum([Forecast])
ELSE sum([Actuals])/sum([Forecast])
END
Like Show 0 Likes(0)

Actions
7. Re: Calculation Error

Chris McClellan Feb 28, 2017 12:58 PM (in response to Satish Katta)

Is that a copy/paste error Satish Katta ? the THEN and the ELSE are the same.
Like Show 0 Likes(0)

Actions
8. Re: Calculation Error

Satish Katta Feb 28, 2017 12:59 PM (in response to Chris McClellan)

SUM() is the key
Like Show 0 Likes(0)

Actions
9. Re: Calculation Error

Chris McClellan Feb 28, 2017 1:05 PM (in response to Satish Katta)

But the THEN and the ELSE are the same, that's a maintenance nightmare
Like Show 0 Likes(0)

Actions
10. Re: Calculation Error

Satish Katta Feb 28, 2017 1:13 PM (in response to Chris McClellan)

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.
Like Show 0 Likes(0)

Actions
11. Re: Calculation Error

Chris McClellan Feb 28, 2017 1:21 PM (in response to Satish Katta)

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)
Like Show 0 Likes(0)

Actions