1 2 Previous Next 20 Replies Latest reply on Nov 29, 2012 6:39 AM by Joe Mako

    Re-creating a formula in Tableau

      I have the formula that I've created in Business Objects XI and want to recreated it tableau but I'm not too sure how. The formula is:

       

      =If((Max([Decision Date]) In ([Applicant ID];[Application Number];[Week]))=[Decision Date];[Decision Grouped])

       

      My trouble is duplicating the In() operator in Tableau. I need a way to look at the most recent Date a Decision is given for each Applicant/Application.

       

      The data in table format looks similar to:

       

      Applicant IDApplication NumberDecision DescDecision DateWeekDecision Grouped
      10620151Conditional Accepted11/06/201138Conditional Accept
      10620151Reject13/09/201152Reject
      10722141Conditional Offer16/11/20091Conditional Offer
      10722141Conditional Accepted04/05/20101Conditional Accept
      10722141Unconditional Firm (UCAS)17/08/20101Unconditional Accept
      10722141Unconditional Accepted18/08/20101Unconditional Accept
      10727144Conditional Offer16/11/20091Conditional Offer
      10727144Conditional Accepted03/05/20101Conditional Accept
      10727144Unconditional Firm (UCAS)15/08/20101Unconditional Accept
      10727144Unconditional Accepted16/08/20101Unconditional Accept

       

      Thanks in advance for the help! Erin

        • 1. Re: Re-creating a formula in Tableau
          Alex Kerin

          Help me understand what the result of the calculation would look like with the data you have above.

          • 2. Re: Re-creating a formula in Tableau

            Thanks for getting back to me.

             

            The idea would be that I could filter by week and I've attached an example of what I would expect the data to look like with different week filters. The calculation above being the 'New Calculation' highlighted in yellow.

             

            Any further questions please let me know.

             

            Thanks again for the help.

             

            Erin

            • 3. Re: Re-creating a formula in Tableau
              Alex Kerin

              Here's a workbook. Two calculations, one to make a unique application number (applicant ID plus application), and another to look for the last date attached to this field.

               

              The table calculation needs to be set so that this unique ID is on the left of the advanced dialog - i.e. the only field that matters when looking for a max date is this unique ID.

               

              I added a clean version of the data that filters out nulls and has the week slider

              • 4. Re: Re-creating a formula in Tableau

                Thanks for your help. I think this should solve the problem but I've tried to recreate your workbook and I'm having troubles with the date dimension. I can't seem to get it to format the way you have. If I change the date to 'Exact Date' it then throws out the layout of the report and if I change the layout in 'Show Me' it changes the date back to it's original format. It's probably something silly that I'm over looking but I'd appreciate the help. I've attached the workbook I've created.

                 

                Thanks again,

                Erin

                • 5. Re: Re-creating a formula in Tableau
                  Alex Kerin

                  A number of things: I assume you want exact date rather than day/month just just in case there is another decision later in the same day. You can still format this as d/m/y but we need the full date to be available to check for max.

                   

                  So for the date, choose exact date, but we don't want to draw a chart (the date is not continuous, it is discrete for that row). See here for a great post about green vs. blue pills: http://www.theinformationlab.co.uk/2011/09/23/blue-things-and-green-things/

                   

                  We change the date to exact date by right clicking, then right click again and change it to discrete and move it back to where it was before in the order.

                   

                  Then we have to sort out the table calculation. When we use the calc to look for the window max  - i.e. the max across a subset of the rows we need to define what that subset is - in this case it's the unique application ID we created. Right click the [Latest Decision] pill on the shelf, choose edit Table Calculation, then on the compute using drop down choose Advanced.

                   

                  In this straightforward Table calc, all we need is the subset on the left, and everything else in the right box (i.e. we are going to ignore all of these when looking for the max)

                   

                  Also, we have to do the same for the Latest Decision on the filter. The easiest way to do this is to fix the pill on the shelf, then move a copy down to the filter and exclude the nulls.

                   

                  Please check to see if my results match your expectation from the data.

                  • 6. Re: Re-creating a formula in Tableau

                    Hi Alex,

                     

                    Thanks for explaining all of that and sending through the link about continuous and discrete; it all has helped explain a lot.

                     

                    This is working as expected now! Thanks again!

                    • 7. Re: Re-creating a formula in Tableau

                      Sorry Alex, but I'm a bit stuck again. I'm trying to create a cross table with the new calculation you've showed me how to do and counting the number of 'Apps ID + No' occurring each week. I keep getting an error message stating that I have overlapping data which I know has to do with the level of detail but I'm not too sure where I'm going wrong. I've attached a sample workbook.

                       

                      I appreciate the help! Thanks again! Erin

                      • 8. Re: Re-creating a formula in Tableau
                        Joe Mako

                        Alex,

                         

                        It is not correct to say that the color of a pill can have an effect on the computation of a table calculation.

                         

                        The fact that a pill is green or blue, or discrete or continuous, has no impact on the computation of table calcuations. Green/blue or discrete/continuous does has an impact on the the visual display and the dialog offered, but not on any computation.

                         

                        What does have an impact on the computation of table calculations is if the pill is an aggregation or non-aggregation.

                         

                        Generally blue pills are not aggregated, and green pills are aggregations, but that is not always the case.

                        • 9. Re: Re-creating a formula in Tableau
                          Joe Mako

                          (this is also posted in your other thread)

                          how about the attached?

                          It uses nested table calculations with different compute using settings, one formula to return a 1 if it is the mark with the most recent date for that account, and another formula to sum up all those ones for each week/group combination. I also placed it on the filter shelf to hide the zeros.

                          • 10. Re: Re-creating a formula in Tableau

                            Hi Joe,

                             

                            Thanks for the help but I'm having an issue with the count and I'm not sure how it's exactly going wrong.

                             

                            When I use your calculations, I'm unable to 'View Data...' behind the number. So what I tried to do to ensure the count was working correctly was implement what Alex had showed me to provide the details and compared it with your count calculation. Unfortunately, the numbers are not matching up.

                             

                            In the attached workbook, Sheet 1 & 2 are showing the list of details (ID, App No, Decision Date, etc.) and then the count. You can see that Sheet 1 has 13 'Awaiting Decisions' but Sheet 2 has 26 'Awaiting Decisions'. Sheet 4 & 5 are just another example but with different dimensions being used in the cross table.

                             

                            Hope you can help and thanks as always!

                            Erin

                            • 11. Re: Re-creating a formula in Tableau
                              Joe Mako

                              The pill on the Text shelf is a nested table calc, meaning there are two table calc formulas in use in that pill, and each formula needs its own compute using settings.  It looks like you only set one. When you open the Edit Table Calculation dialog, you will see a drop-down to select the formula you want to set the partitioning for.

                               

                              For "Latest Decision Count":

                              Compute using: Advanced

                              Compute using: Decision Date, App ID+ No

                              Order Along: Automatic

                               

                              For "Latest Decision Calc 1"

                              Compute using: Advanced

                              Compute using: Decision Date, Week, Decisions Grouped

                              Order Along: Automatic

                               

                              (sort order in list box matters)

                               

                              You will then need to delete the table calc pill from the filter shelf, and re-ctrl-drag-and-drop it from the Text to Filter shelf, setting it to At Least 1.

                               

                              This is done in the attached.

                               

                              Also you will want to update to the latest version of Tableau:

                              https://licensing.tableausoftware.com/esdalt/

                              • 12. Re: Re-creating a formula in Tableau
                                Laura East

                                Hi Joe/Alex,

                                 

                                Erin has asked me to look after this post while she is on leave.

                                 

                                All the work on the count etc. has gone very well with regards to Tableau.

                                 

                                Unfortunately, there's a glitch in the time-stamping in the system that our data in Tableau originally comes from. Two different decision descriptions can come through on the same dd/mm/yy hh:mm:ss stamp for the application, so when imported, Tableau (correctly) counts them each as a separate line as it can't tell which is the latest. This left us with about 800 extra records in Offers and some in other columns.

                                 

                                I've attached a small file where I've highlighted the first examples that came up: the cause in the Decision Date column is pretty clear. NB this is some of the data that feeds into Tableau, not a Tableau export.

                                 

                                We do now have our technical team here working on fixing the time-stamp output in our own system. What we were wondering however, as Tableau is more powerful, is:

                                 

                                Is there a way to get the Tableau calculation to make a final discrimination under these circumstances? With the logical meaning 'if applicant ID, application number, entry term short, application year, week, and decision date are all the same, count [given decision code/desc] and do not count [given decision code/desc]'. E.g. for 90270, the lines are identical, except for codes 03 and 05; in this column, we want to count the line for 05 and not 03.

                                 

                                I think one of my colleagues may have made some progress with COUNTD but it does not interpret the data in quite as much detail.

                                 

                                I do hope this makes sense, let me know if not.

                                 

                                Thanks again

                                Laura

                                • 13. Re: Re-creating a formula in Tableau
                                  Joe Mako

                                  The route or method depends on the logic you want for handling these multiple codes.

                                   

                                  In the attached, when there are multiple codes, only the highest code value is counted.

                                   

                                  There are other routes if you need more sophistication.

                                  • 14. Re: Re-creating a formula in Tableau
                                    Laura East

                                    Thank you for this (we've been side-tracked a little while...). 5 of the older codes still existing are alpha rather than numeric, where would it rank them?

                                    1 2 Previous Next