6 Replies Latest reply on Feb 24, 2016 2:23 PM by jitan.chainani

    Merging actuals & projections into one visualization

    jitan.chainani

      HI Guys,

       

      A little complex scenario that I need you help with - There are 2 different data sets 1) Current Year 2) Prior Year for same show in different country and I need to merge all in one visualization -

       

      1) This data set has current year details . Below is a sample: -

       

      Country Item Date Current Year USD Week Release Date Day No

      Japan 10 11/1/2015 $2,500 Week 1 11/1/2015 1

      Japan 10 11/2/2015 $2,600 Week 1 11/1/2015 2

      Japan 10 11/3/2015 $2,700 Week 1 11/1/2015 3

      Japan 10 11/4/2015 $2,800 Week 1 11/1/2015 4

      Japan 10 11/5/2015 $2,900 Week 1 11/1/2015 5

       

      2) This data set has previous year details  with a different release date -

       

       

                                                                                      

      CountryItemDateCurrent Year USDWeekRelease DateDay No
      Japan1011/1/2015$                    2,500Week 111/1/20151
      Japan1011/2/2015$                    2,600Week 111/1/20152
      Japan1011/3/2015$                    2,700Week 111/1/20153
      Japan1011/4/2015$                    2,800Week 111/1/20154
      Japan1011/5/2015$                    2,900Week 111/1/20155

      Country Item Prior Year Prior Year USD Week Release Date Day No

      Japan 10 11/5/2014 $3,000 Week 1 11/5/2014 1

      Japan 10 11/6/2014 $2,500 Week 1 11/5/2014 2

      Japan 10 11/7/2014 $2,400 Week 1 11/5/2014 3

      Japan 10 11/8/2014 $2,300 Week 1 11/5/2014 4

      Japan 10 11/9/2014 $2,200 Week 1 11/5/2014 5

       

      Both the data sets are stored as raw data in the data base and the requirements for visualization is  -

       

      1) Based on the previous year calculate the projections for this year before the current year's launch

      2)After the release projections are overwritten by the correct numbers

      3) Show the current year vs. prior year by Day No - (In this scenario both years release date is different so  I have to manually format this in excel.)

       

      Is there a way in Tableau to calculate projections if the raw data is in database and also release date is different? Currently I am doing this in spreadsheet but need to automate it in tableau so that if user is launching next year and need projections from 2016 data it can be done without any manual calculation.

       

      I have attached a spreadsheet for your reference.

       

      Thanks for your help and suggestions.

       

      JC

        • 1. Re: Merging actuals & projections into one visualization
          Shinichiro Murakami

          I created something, but  I could not understand what should be the "Projection".

          The file only shows the first 2 weeks, then I only put first two weeks, plus not sure how to handle % column shown as "Projection".

          Anyway, you can handle these as you like.

           

           

           

          Create several calculated field

          <<< Previous Data>>>

          [Release Date Calc] 

          {fixed [Country],[Item]:min([Prior Year])}

           

          [Day No (calc)]

          [Prior Year]-[Release Date Calc]+1

           

          [Week Calc]

          "week "+str(int(([Day No (calc)]-1)/7)+1)

           

          [WW1.2] // in previous data source

          {fixed [Country],[Item]:sum(if [Week]="Week 1" or [Week]="Week 2" then [Prior Year USD] end)}

           

          [Projection]  // in previous data source

          sum([Prior Year USD]/[WW1.2])

           

           

          <<<Current Data>>>

          [Release Date (calc)]

          {fixed [Country],[Item]:min([Date])}

           

          [Day No (calc)]

          [Date]-[Release Date (calc)]+1

           

          [Week Calc]

          "week "+str(int(([Day No (calc)]-1)/7)+1)

           

          Set Previous as Primary Data source and Current as 2nd source.

          And link with "Country", "Item", and "Day No (Calc)"

           

           

          <<<In previous data >>>

          [Current Overwrite]

          ifnull(SUM([Current].[Current Year USD]),sum([Prior Year USD]))

           

          *** I filtered current Data with "<12/9" to validate current overwrite ****

           

           

          Thanks,

          Shin

           

           

          9.0 attached.

          • 2. Re: Merging actuals & projections into one visualization
            jitan.chainani

            Hi Shin,

             

            Thanks for the solution. I am going to try this now..

             

            Quick Question -  The last step bascially overwrites the actuals with previous if actuals are available for the same day. Right?

             

            Thanks,

            JC

            • 4. Re: Merging actuals & projections into one visualization
              jitan.chainani

              Thanks Shin! This was very helpful. Based on your suggestions I actually created a visualization that does not need an over writes. would rather show side by side.

               

              One more questions.....

               

              I have Week 1  and Day No from  1- 7

                          Week 2 and Day No from 8 - 14

               

              How can I just make both weeks from 1 - 7 so that the data i aligned.

               

              Thanks,

              JC

              • 5. Re: Merging actuals & projections into one visualization
                Shinichiro Murakami

                [Day No (Calc 1-7)]

                ([Day No (calc)]-1)%7+1

                 

                % = MOD of Excel

                % (modulo)   // Operators

                This calculates a numeric remainder. For example, 5% 4 = 1.

                1 of 1 people found this helpful
                • 6. Re: Merging actuals & projections into one visualization
                  jitan.chainani

                  Thanks Shin! That worked well.

                   

                  One more quick question on projections -

                   

                  IF I have an estimated amount for a country Let's take an example of Japan 3500 and i know the % of every day that' been projected for Week 1 and Week 2, how will i calculate the projected amount in tableau

                   

                  Week 1  Estimated Amount =  3500

                                  % Forecast               Projected Amount

                  Day 1             10                                  3500*10%

                  Day 2             20                                  3500*20%

                  Day 3             30

                  Day 4             10

                  Day 5              5

                  Day 6              6

                  Day 7             9