6 Replies Latest reply on Jun 22, 2018 5:07 AM by Pak Hang Leung

    How to use the calculation field to compute the average data for past couple months

    Pak Hang  Leung

      Hi all

       

      In my department, there is a own prediction model, and I have to visualize it through tableau.

      The tableau table is similar as follows:

      DateSales
      Jan-1710000
      Feb-1712000
      Mar-1715000
      Apr-1713500
      .....
      Jan-1818500
      Feb-1819500
      Mar-1820500
      Apr-1822500

       

      So now I have to make the forecasting for May-18.

      The formal that we use is the growth rate is: (the average sales for past 3 months - the average sales for past 3 months in same period in last year) + The sales for the certain month in last year.

       

      The first step that I want to do is, to look up the figure for the past 4 months.

      I tried to use the following function to extract the figure last months by setting new variables and the time parameter

       

      IF DATETRUNC('month', [Visit month]) = DATETRUNC('month', DATEADD('month', -1, TODAY()))

      THEN [total billings] END

       

      And turn out I got sth like this

      DateTotal salesSales monthSales last 2 month
      Feb-181950019500
      Mar-182050020500
      Apr-1822500

       

      And if I want to but a calculated field and return the figure i.e the average for the past 4 months and the average for the past 4 months in last year, how should I do that?

      Or any better solution that I can use to solve the problem?

      Many thanks for your help in advance!

        • 1. Re: How to use the calculation field to compute the average data for past couple months
          Mavis Liu

          Hi Pak,

           

          Maybe try something like this for the previous 3 months average:

           

          avg(if

          DATETRUNC('month', DATEADD('month', 4,[DATE])) > DATETRUNC('month',{ FIXED : MAX([Month])})

          and DATETRUNC('month',[DATE]) < DATETRUNC('month', { FIXED : MAX([Month])})

          = true

          then ([VALUE)

          END)

           

          For the previous year's 3 months:

           

          avg(if

           

          DATETRUNC('month', DATEADD('year',1, DATEADD('month', 4,[Month]))) > DATETRUNC('month',{ FIXED : MAX([Month])})

           

          and DATETRUNC('month', DATEADD('year',1,[Month])) < DATETRUNC('month', { FIXED : MAX([Month])})

           

          = true

           

          then ([Gross Adds])

           

          END)

           

          Thanks,

           

          Mavis

          • 2. Re: How to use the calculation field to compute the average data for past couple months
            Pak Hang  Leung

            Hi Mavis , first thanks for your prompt help! But do not know why the figure is not accurate .... And can you explain more about the function =) ? Because I am nearly totally new in this area and would like to know more how the problem can be solved. Thanks again!

            • 3. Re: How to use the calculation field to compute the average data for past couple months
              Mavis Liu

              Hi Pak,

               

              Have you got the dataset which you can attach? Then if you could let me know what your expected result is that would be great.

               

              Note: Date truncate truncates the date down to the date part specified. E.g. 05/06/2018 using a date trunc for month becomes 01/06/2018.

               

              So for the previous 3 month's average:

              avg(if

              DATETRUNC('month', DATEADD('month', 4,[DATE])) > DATETRUNC('month',{ FIXED : MAX([Month])})

              and DATETRUNC('month',[DATE]) < DATETRUNC('month', { FIXED : MAX([Month])})

              = true

              then ([VALUE)

              END)

               

              The month-year of the date has an extra 4 number of months added to it. When this is greater than the fixed max month-year, then it will be set as true. The fixed calculation is an LOD calculation which ignores all filters and only looks at the very maximum date in the WHOLE dataset.

               

              Say the max date in the dataset is June 2018:

               

              If we add 4 months to March, this becomes 'July',  April, this becomes 'August', May becomes 'September' and June becomes 'October'. All these months are greater than June 2018 so are valid. However, then it becomes a bit endless because July can then become November, August becomes December etc... and these are greater than June.

               

              If you want to exclude the month of June in the view when you're looking at the previous 3 months only, then that's what the line: and DATETRUNC('month',[DATE]) < DATETRUNC('month', { FIXED : MAX([Month])})  is for.

               

              Thanks,

               

              Mavis

              • 4. Re: How to use the calculation field to compute the average data for past couple months
                Pak Hang  Leung

                Hi Mavis

                 

                That's awesome! I tried a bit further by googling and tried to summarize the data in the table, and the workbook is attached as your reference.

                I tried to use the figure i.e (Avg sales in 3 months - Avg.sales in 3 months in last year) + Same month in last year in the variable Prediction figure , there is no figure returned in the table.

                If I want to come up with the figure, and put it in a  new date record e.g June 2018, July 2018, Aug 2018 etc as a kind of forecast, how can I make it?

                 

                Again, thanks again and you are simply great =)!

                 

                Have a nice day,

                Pak Hang

                • 5. Re: How to use the calculation field to compute the average data for past couple months
                  Mavis Liu

                  Hi Pak,

                   

                  Apart from the already built in forecasting within Tableau, this is going to be tricky as you want it calculated in a really specific way, I'd suggest using multiple table calculations to do this.

                   

                  Please see the attached, so to predict June 2018, this is what it works out:

                   

                  Current Year 3 Previous 3 months average: 676,667

                   

                  Current Year 3 Previous 3 months average: 423,333

                   

                  Difference between the two is 253,333

                   

                  Same month last year: 400,000

                   

                  So the forecast would be 653,333

                   

                  Is that right? Please see attached.

                   

                  To be honest, Tableau isn't really the tool to do the forecasting you want.

                   

                  Thanks

                  Mavis

                  • 6. Re: How to use the calculation field to compute the average data for past couple months
                    Pak Hang  Leung

                    Hi Mavis

                     

                    Yes, that's exactly what I need =)

                    Just one more question: When I tried to extend the prediction by changing the date prediction calculation,

                    It returns the same number of rows e.g February 2017 to June 2018, or July 2018 to November 2018.

                    If I want to have like 6 months in advance i.e with the same algorithm and show the data from January 2017 to December 2018, how should I make the adjustment?

                     

                    P.S I also find out the problem that you mentioned.....but still, many thanks and it's a great alternative =)

                     

                    Pak Hang