11 Replies Latest reply on May 29, 2013 3:11 PM by Siddharth Surana

    Calculated field & Table calculation problem

    Siddharth Surana

      Hey Guys

       

      My final result is fairly straight forward but seems like I am getting stuck somewhere.

       

      Scenario: I want to calculate an Adjusted Forecast column within Tableau keeping in mind the hierarchy and roll up I have on Order to App. So this field does the following:

       

      Adjusted Forecast = (Total Ordered to Date - Total Delivered to date) / Number of days remaining from today to end of order date.

       

      I am having problem with the dates as the calculation I have does not calculates it from today but starts the calculation from any Null value it sees from in the Delivered Column. In my actual data I will have days when there is delivered / ordered for a period of time and then continues after some days.

       

      (Also, if you notice the roll ups are not happening the right way. It will be great if anyone can help in this regards too)

       

      Can any one fix this issue. Would really appreciate it.

       

      I have attached both the Data and the twbx

       

      Sid

        • 1. Re: Calculated field & Table calculation problem
          Shawn Wallwork

          I'm bumping this one just on general principle. I haven't opened your workbooks, but it looks like you've provided the necessary materials to solve this problem. Hope you get someone to help.

           

          --Shawn

          • 2. Re: Calculated field & Table calculation problem
            Jonathan Drummey

            Maybe I'm not understanding this, why can't you use a (RUNNING_SUM(Orders)-RUNNING_SUM(Delivered))/(days remaining)? It looks like your data is missing dates within the given App/Order, so you'd need to turn on Show Missing Values for your dates to pad them out, but that seems like that calc would get you there.

             

            I also don't understand what the order date is and how it's determined.

             

            And you noted, "(Also, if you notice the roll ups are not happening the right way. It will be great if anyone can help in this regards too)." This is a case where providing the right answers for the sample data would be helpful to match them up.

             

            Jonathan

            • 3. Re: Calculated field & Table calculation problem
              Siddharth Surana

              Hi Jonathan 

               

              Sorry for the late reply and thanks for taking interest in the problem 

               

              So the problem seems very simple but for some reason I am running into problems with my current calculated fields.  

               

              My final intent is to calculate the forecast for the order / app based on how much is yet to be delivered. But there are various concerns. Like the App / Order Date is random. There are some days we have orders and others where we have no deliveries.

               

              So taking this into consideration I want the field to calculate all future forecasts based on the number of days remaining. I am attaching an excel sheet that has the data and desired outcome in excel. Please do have a quick look and see if this can be achieved.

               

              Thanks

               

              Sid

              • 4. Re: Calculated field & Table calculation problem
                Jonathan Drummey

                Hi Sid,

                 

                I took another look at your spreadsheet and saw the formula, now I understand how you were parceling out the forecast. In the attached I did the following:

                 

                - Turned on Show Missing Values for the DAY(Date) field to pad out the missing dates

                - used the LAST() calc to generate the days remaining, based on LOOKUP(SUM([Ordered]),0) being Null.

                - used the formula

                IF ISNULL([Days Remaining]) THEN

                    SUM([Delivered])

                ELSE

                    (TOTAL(SUM([Ordered]))-TOTAL(SUM([Delivered])))/WINDOW_MAX([Days Remaining])

                END

                 

                This returns proper values. The compute using for both table calcs is an Advanced... with App ID/Order ID/Date, sorting automatic, restarting every Order ID. If I turn off Show Missing Values I see exactly what is in your spreadsheet, turning the domain padding back on returns different values, of course.

                 

                Hope this helps!

                 

                Jonathan

                 

                [20130528 provided a solution instead of more questions]

                • 5. Re: Calculated field & Table calculation problem
                  Siddharth Surana

                  Hi Jonathan

                  This is great and helps me avoid the 7+ calculated fields I had to do in my previous attempt. Your approach is simple and really helpful.

                  There were two considerations here in this approach that I wanted to clarify:

                   

                  1. We assume that the calculation starts where it is Null. What if there are Null values in between in the Delivered Column instead of a 0. Will this work even then? Like -

                  5/21/2013 - 10000

                  5/22/2013 - Null

                  5/23/2013 - 0

                  5/24/2013 -50000

                   

                  2. And I wanted to know if this would also work if there was a roll up. (I create a hierarchy App ID - Order ID). This does not seem to work in that case  Your answer was really helpful and hope you have some advice on the these questions

                   

                  Thanks again

                  Sid

                  • 6. Re: Calculated field & Table calculation problem
                    Jonathan Drummey

                    1. Currently the calc expects all values to be non-Null until the last range of dates. What do you want to happen if there is a Null (or set of Null's) in an earlier range? Should the Adjusted Forecast field just return 0 in that case?

                     

                    2. The table calculation uses an Advanced Compute Using, so when you change the hierarchy level then there are dimensions either added to the partitioning or missing from the addressing for the table calculation and it returns non-desired results. The general option in this case is to not use Tableau's drill-downs but instead to use a parameter to either swap out dimensions & calc results, or to swap out two separate worksheets on a dashboard.

                     

                    Jonathan

                    • 7. Re: Calculated field & Table calculation problem
                      Siddharth Surana

                      Hey Jonathan

                       

                      I believe that it can return 0's for all nulls before the last date. But a much more efficient formula would be to calculate it using the Sysdate (Now) function. Maybe that is a better option and it will avoid the hassle of Nulls and 0's. Do you think that is a good idea?

                       

                      Number of Days remaining = Last day of the order - Sysdate (Now)

                       

                      Also, my company is pretty specific how they want the data and drill downs is a very important part of the report. The parameter option looks fine and the Dashboard is an option, but can anything be done on this drill down?

                       

                      Thanks

                      Sid

                      • 8. Re: Calculated field & Table calculation problem
                        Jonathan Drummey

                        If you can calculate the days remaining in the data source, then that avoids the complexities of table calculations and can certainly make life easier.

                         

                        As for the drill downs, are your users requiring Tableau's actual drill downs or do they just want to see both results? In the latter case, then you can use either of those solutions and the users will get the drill down effect they want.

                         

                        Jonathan

                        • 9. Re: Calculated field & Table calculation problem
                          Siddharth Surana

                          Hey Jonathan

                           

                          Sorry was trying out various solutions to the problem. So I wanted to know what you feel would be the best way to do the calculation to find out the number of days from Today to the end of the Order ?

                           

                          Also, I have 2 kinds of users. One would want to see only one field and the others may/ may not want to drill down. I am sure the Drill Down is required. I just confirmed this with the Business users. Is there a way to work with this?

                           

                          Thanks Jonathan

                          Regards

                          Sid

                          • 10. Re: Calculated field & Table calculation problem
                            Jonathan Drummey

                            If you can do it in the data source, that would be my personal preference, it makes life easier for the rest of the calcs. As for the drill down, you'll have to use one of the two workaround techniques I described or do further work in the data query to not require table calcs, Tableau's hierarchies and table calcs don't work together in the way that is needed for this worksheet.

                            • 11. Re: Calculated field & Table calculation problem
                              Siddharth Surana

                              Thanks a lot Jonathan  I will try and work with this  Regards Sid