4 Replies Latest reply on Sep 7, 2016 1:38 PM by Caleb Smith

    Help applying a predictive model (calculated field) to worksheet

    Caleb Smith

      Hi all,

       

      This is my first attempt to apply a predictive model to data in Tableau and I need some help. I've developed a function to predict a value 30 days in the future with satisfactory results and would now like to visualize it in Tableau.

      First, the equation:

      I have two independent variables and a dependent variable. The DV is "y". The IVs are "days" (1, 2, 3,...n) and the value of "y" 30 days ago (represented here as "delay(y, 30)"

       

      I would like to do two things:

      1. Create a worksheet that displays the observed results vs the predicted results, similar to the image below (where blue = observed, red = predicted)

      2. Create a way to extrapolate the predicted value out arbitrarily far into the future. For example, my equation uses the y value from 30 days ago to predict a current value of y. This means that when I reach the end of my observations I can only show a value 30 days into the future (current day + 30). I would like to figure out a way to feed the predicted values back into the equation in order to go further into the future. I would like to specify a value of y and then show the day upon the model predicts that y will be reached. The further into the future this day lies, the more the accuracy of the model will suffer, but that's OK and expected. I'd just like to know how to do it.

       

      WHAT I'VE DONE SO FAR:

      I've created a workbook with some actual data that I've attached here. In this workbook I've created a calculated field called "Prediction Model," which translates the equation I listed above into the language of Tableau.

       

      WHERE I NEED YOUR HELP:

      1. How can I apply my calculated field to the observed data to get a graph like the one above?

      2. How can I feed the predicted values back into my model in order to extrapolate further into the future?

       

      Please let me know if anything is unclear or if I've left out any important information. I've used Tableau quite a bit for simple visualizations but this is the first time I've tried to visualize a predictive model in the tool. Any help you can provide would be greatly appreciated!

        • 1. Re: Help applying a predictive model (calculated field) to worksheet
          Benjamin Greene

          To answer your first question, right click both Y and Prediction Model and change the data type to Number (Decimal). Both fields need the same data type for this to work. Now Drag Prediction Model to Rows, right-click the pill and select Dual Axis. Now, right click the Prediction Model axis, select Synchronize Axis and then deselect Show Header.

           

          Comparing the Predicted Values to the actual Y values, it appears that your calculated field isn't working correctly. In the attached packaged workbook, I made a guess at the calculation you were looking for:

           

          // y = max(atan(delay(y, 30)^2), round((delay(y, 30)*max(days, delay(y, 30))*less_or_equal(31, days) - 23)/delay(days, 20)))

           

           

          //Max of two values

          MAX(

          //Value 1 is the ATAN of y 30 days prior to observation date squared

          ATAN(LOOKUP(ATTR([Y]),-30)^2),

          //Value 2 is the value of y 30 days prior to observation date * the MAX of either the current days or y 30 days prior to observation

          // * 1 or 0 - 23 and divided by the value of days 20 days prior to observation

          ROUND((LOOKUP(ATTR([Y]),-30)*MAX(ATTR([Days]),LOOKUP(ATTR([Y]),-30))*ATTR(IIF(31<=[Days],1,0))-23)

          /

          ATTR([Days]-20)

          ))

          1 of 1 people found this helpful
          • 2. Re: Help applying a predictive model (calculated field) to worksheet
            Caleb Smith

            Thanks, Benjamin! This was really helpful. It resolves the first issue completely. Now I just need to figure out how to address the second issue (using results of the prediction model to extrapolate future values).

            • 3. Re: Help applying a predictive model (calculated field) to worksheet
              Benjamin Greene

              Right. OK. So I think that kind of depends on how far out into the future you want to go. The easiest way would be to manually add future dates and days for each Id into your raw data with blank values for all the other fields. This would automatically allow you to forecast out an additional 30 days (722 through 751) using the Prediction Model field, as we have Y values all the way up through day 721. However, it sounds like you want to extrapolate even further using the predicted values in place of the Y values. Tableau does not allow you to circularly reference previous values of a calculated field within itself, so you would need to make another calculated field that uses the Y values to build the forecast wherever possible, and then uses the predicted values after that. Unfortunately, even this workaround would only give you another 30 observations (752 through 781), since we run out of predicted values at day 751. I'm sure you're starting to pick up on the pattern here; you would need to create another new calculated for every 30 days you want to extrapolate. This is tedious, but this general idea is on sheet 2 of this workbook.

              1 of 1 people found this helpful
              • 4. Re: Help applying a predictive model (calculated field) to worksheet
                Caleb Smith

                Ah, I see. This makes sense and should be find for what I'm doing. Thanks again, Benjamin for all your help!

                 

                Noah Salvaterra, I recently read your post on Creating data, multi-step recurrence relations, fractals and 3D imaging… without leaving Tableau – by Noah Salvaterra | … and I wondered if it might be possible to apply your approach here? I looked at the solution workbook you posted for Fibonacci and it looks like it would be applicable but to be honest I don't understand it well enough to modify it. Do you think it would be possible to use your method to iteratively extend the 30 day prediction in the workbook I've attached?