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

    Calculation Error

    Bianca Reddick

      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
          Deepak Rai

          Bianca,

          See Screenshot and Calculation

          Thanks

          Deepak

          • 2. Re: Calculation Error
            Bianca Reddick

            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.

            Capture.JPG

             

             

            Thank you,

             

            Bianca

            • 3. Re: Calculation Error
              Deepak Rai

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

              • 4. Re: Calculation Error
                Bianca Reddick

                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:

                Capture.JPG

                 

                Project
                  Number
                PM LeadFunding Type Jan-2016_F JAN-2016_A FEB-2016_F FEB-2016_A
                C161020AdminO&M $               44,396 $                         -   $               44,396 $                        30
                C161002AdminO&M $                 1,000 $                         -   $                 1,000 $                  1,000
                C161070Service CentersCapital $                 1,600 $                  3,710 $                 1,600 $                  4,445
                C161070Service CentersO&M $                 1,600 $                  2,630 $                 1,600 $                  1,120
                C161100Service CentersCapital $                 7,982 $                        60 $                 7,982 $                     176
                C161110Service CentersCapital $               14,400 $                13,789 $               14,400 $                  4,898
                C161300Corporate OfficesO&M $                 8,964 $                16,181 $                 8,964 $                  4,066
                C161530Service CentersO&M $                 1,600 $                10,571 $                 1,600 $                  2,809
                C161530Service CentersCapital $                 1,601 $                11,661 $                 1,601 $                  3,606
                C161570MajorsCapital $                 5,000 $                      504 $                 5,000 $               19,160
                C161610Service CentersO&M $                     500 $                         -   $                     500 $                         -  
                C161680Service CentersO&M $                 1,000 $                         -   $                 1,000 $                         -  
                C161750Service CentersCapital $                 5,000 $                         -   $                 5,000 $                         -  
                C161760MajorsO&M $                 1,500 $                         -   $                 1,500 $                         -  
                C161770MajorsCapital $               25,000 $                17,417 $               25,000 $                  1,062
                C161780Service CentersBU Funded $               82,573 $                         -   $               82,573 $                         -  
                C161790Service CentersBU Funded $               25,000 $                         -   $               25,000 $                         -  
                C161800MajorsCapital $                 5,000 $                  1,109 $                 5,000 $                     617
                C161890MajorsCapital $                 5,000 $                      263 $                 5,000 $               17,166
                C161900MajorsCapital $                 5,000 $                  1,159 $                 5,000 $                  6,300
                C161910Service CentersCapital $                     500 $                         -   $                     500 $                         -  
                C162000Corporate OfficesCapital $                 1,000 $                         -   $                 1,000 $                         -  
                C162010Corporate OfficesO&M $               25,212 $                  6,789 $               25,212 $                  4,588
                C162010Corporate OfficesCapital $            125,000 $              160,928 $            125,000 $                  4,898
                C162040Corporate OfficesCapital $                 2,000 $                         -   $                 2,000 $                  1,959
                C162060Corporate OfficesO&M $               29,982 $                  7,179 $               29,982 $                  1,959
                C162060Corporate OfficesCapital $                 1,200 $                28,318 $                 1,200 $                  4,634
                C162090Corporate OfficesO&M $               16,435 $                16,435 $               16,435 $                  1,469
                C162100MajorsCapital $                 5,000 $                      120 $                 5,000 $                  5,267
                C162200Service CentersBU Funded $               71,589 $                         -   $               71,589 $                         -  
                C162200Service CentersBU Funded $               10,471 $                         -   $               10,471 $                         -  
                C162260Corporate OfficesCapital $               10,000 $                      667 $               10,000 $                  9,719
                C162280Corporate OfficesCapital $               21,015 $                  1,985 $               21,015 $               15,422
                C162300Service CentersCapital $                 1,000 $                         -   $                 1,000 $                         -  
                C162320Service CentersCapital $                     500 $                         -   $                     500 $                         -  
                C162330Service CentersCapital $                 1,000 $                         -   $                 1,000 $                         -  
                C162380Service CentersCapital $                 2,500 $                         -   $                 2,500 $                         -  
                C162390Service CentersO&M $                 1,000 $                  1,300 $                 1,000 $                     980
                C162400Service CentersBU Funded $               16,250 $                         -   $               16,250 $                         -  
                C162400Service CentersBU Funded $                 8,750 $                         -   $                 8,750 $                         -  
                C162480Service CentersCapital $                 1,000 $                         -   $                 1,000 $                         -  
                C162500MajorsCapital $               13,171 $                  2,329 $               13,171 $               17,578
                C162520MajorsCapital $                 8,220 $                  2,780 $                 8,220 $                  2,408
                C162530Service CentersCapital $               48,500 $                48,500 $               48,500 $                         -  
                C162540MajorsCapital $               10,000 $                      152 $               10,000 $                  6,243
                C162550Service CentersCapital $                 1,000 $                         -   $                 1,000 $                         -  
                C162560Service CentersCapital $                 1,000 $                         -   $                 1,000 $                     980
                C162570MajorsCapital $               10,000 $                  1,105 $               10,000 $                  2,328
                C162600Service CentersCapital $                 1,000 $                         -   $                 1,000 $                         -  
                C162610MajorsCapital $                 9,324 $                  3,676 $                 9,324 $                  2,657
                C162630MajorsCapital $                 5,000 $                  1,830 $                 5,000 $                  2,088
                C162670Service CentersCapital $                     500 $                         -   $                     500 $                         -  
                C162700Service CentersBU Funded $               10,000 $                         -   $               10,000 $                         -  
                C162700Service CentersBU Funded $                 2,500 $                         -   $                 2,500 $                         -  
                C162730Service CentersCapital $                 1,000 $                         -   $                 1,000 $                         -  
                C162740Service CentersCapital $                 1,000 $                         -   $                 1,000 $                         -  
                • 5. Re: Calculation Error
                  Chris McClellan

                  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
                    Satish Katta

                    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
                      Chris McClellan

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

                      • 8. Re: Calculation Error
                        Satish Katta

                        SUM() is the key

                        • 9. Re: Calculation Error
                          Chris McClellan

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

                          • 10. Re: Calculation Error
                            Satish Katta

                            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
                              Chris McClellan

                              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)