8 Replies Latest reply on Dec 13, 2018 1:14 AM by Manas Datta

    VLookup Type Calculation

    Manas Datta

      Hi group, this is the first time I'm actually posting something. Just starting to "really" use Tableau, and I've hit a bit of a roadblock, I'll make my query as simple and clear as possible:

       

      The situation: Currently using Excel to forecast quarterly sales, but want to move this to Tableau. 

      • I am using Q4 2017 data to generate a pattern of daily sales. I am then applying that pattern to Q4 2018 QTD data.
      • The forecast combines QTD data (which I update weekly via a CSV), and forecast for the remainder of the quarter.
      • For the remainder of the quarter, I'm applying the 2017 "pattern" to 2018 "actuals" to forecast where we will end the quarter.

       

      Methodology for calculating the remainder of the quarter:

      Example:

      • For EOQ Forecast: On the 11th of November 2017, we hit 20% of Q4 sales. On the 11th of November 2018, we made $50,000. My forecast for end of quarter is $50,000/20% or $250,000.
      • For the daily breakdown: On the 20th of November 2017, we hit 30% of sales. Then my forecast for 20th of November 2018 will be 30%*$250,000 (my EOQ forecast from the step above).

       

       

      My output in Excel looks like this (based on actuals data till the 11th of November):

       

      I've made very little progress in Tableau on this unfortunately. My challenge is this:

      1. I need to take the corresponding %ge figure from 2017 data based on the max of the 2018 actuals running total, so I can calculate my End of Q forecast.
      2. I need to then apply this EOQ forecast to the remainder of the Q to get my daily forecasts.

       

      Anyone got any guidance on how to tackle this? Sample packaged workbook attached.

        • 1. Re: VLookup Type Calculation
          swaroop.gantela

          Manas,

           

          Welcome to the Forum.

           

          Well, I don't think I got there, but maybe the below can give ideas.

           

          There is very likely a more straightforward way to go about this,

          particular since you have nicely unioned the data which gives you the benefit of

          [Table Name] to work with.

           

          I instead took a roundabout method of many steps:

          - creating a data scaffold of all possible dates

          - aggregating on the day level the 2017 and 2018 data separately

          - joining the 2017 and 2018 data to the scaffold

            (I think this is what allows for getting the running 2017 % and applying it to  2018)

          - calculating the running 2017 %

          - calculating the max 2018 date

          - calculating the value at the max 2018 date

          - calculating the projected quarter value

          - show either the true 2018 value or the projected value

           

          I also made an attempt to have it restart every quarter, but will need more data to check that.

           

          I can describe these steps in further detail, if needed.

          First just wanted to see if this was in the ballpark for getting the right numbers.

          Then wanted to see if this approach is feasible to you, depending on the size and type of your dataset.

           

          Please see the workbook v10.3, flow, and scaffold attached in the Forum Thread:

          VLookup Type Calculation

           

          287933scaffold.png

          1 of 1 people found this helpful
          • 2. Re: VLookup Type Calculation
            Manas Datta

            Thanks - based on your summary so far, I think I can get to the end, but let me take a look at the stuff you attached. I appreciate tremendously the work you've put into this! Thank you so much! I'll update this thread once I make progress.

            • 3. Re: VLookup Type Calculation
              Manas Datta

              I worked on this last night and it works like a charm. I love the relatively simple approach, so even though there are a few steps, they're easy to understand. Thank you again for your help.

              • 4. Re: VLookup Type Calculation
                swaroop.gantela

                Manas,

                 

                Thanks for the follow-up, I'm glad that was helpful.

                 

                Shinichiro Murakami:

                If I may so request, I would be grateful for your advice regarding this union problem.

                The question in summary: 2017 data has been unioned to 2018 data.

                For those dates in 2018 that haven't occurred yet, need to generate a projected number

                based on what happened up to that same date in 2017 (running % of a quarter's total).

                I tried using the [Table Name] field, but got tangled in the table calculations.

                So I went a different longer route, but it felt that there should be a more straightforward way

                using the [Table Name] field.

                • 5. Re: VLookup Type Calculation
                  Shinichiro Murakami

                  Don't have time to analyze your answer.

                  This is starting from scratch.

                   

                  This includes complicated table calc and only one step miss makes results completely different.

                  Make it sure you follow the EXACTLY same steps.

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  Thanks,

                  Shin

                  3 of 3 people found this helpful
                  • 6. Re: VLookup Type Calculation
                    swaroop.gantela

                    Shinichiro,

                     

                    Thank you very much for your time, your solution, and your detailed and clear explanation.

                    I have bookmarked this thread for future reference.

                     

                    Your approach is much better because it doesn't require a date-scaffold or Prep.

                    I thought that it was set up nicely for your Union brilliance, but I see now that I

                    had many more steps to go.

                     

                    Thank you again.

                    1 of 2 people found this helpful
                    • 7. Re: VLookup Type Calculation
                      Shinichiro Murakami

                      You are welcome.

                      Shin

                      • 8. Re: VLookup Type Calculation
                        Manas Datta

                        Shin,

                         

                        Just wanted to add my thanks to you for this great reply. As I get more comfortable with these calculations, this thread is going to be like a reference point for my future work.

                         

                        Happy Holidays to everyone reading this :-).

                         

                        Manas