1 2 Previous Next 24 Replies Latest reply on Dec 5, 2017 9:52 AM by XIALIN ZHOU

    Loop logic functions in Tableau

    XIALIN ZHOU

      hi all, I am trying to create some logic statements in Tableau, and they are loop logic that have connections with each other. Following are metrics I tried to created and their relationship

       

      Blue words are metrics in formula. Red words are metrics not calculated yet while processing this step

       

      1:  Calculate Non Billable Impressions

            if Variance in Authentic-Viewable % <0, then 0

            else if Variance in Authentic-Viewable % >=0

            then = Authentic Impressions(DV)* Variance in Authentic-Viewable %

       

      2: Calculate Billable Impressions

           If Authentic Impressions(DV)Non Billable Impressions> New Weekly Imp Goals

           then  = New Weekly Imp Goals

          else Authentic Impressions(DV)Non Billable Impressions

       

      3: Calculate TENTATIVE Billable Media

          = CPM * Billable Impressions

       

      4: Calculate Weekly dollars to be rolled over

           if TENTATIVE Billable Media < New Weekly Budget Goals

           then = New Weekly Budget Goals - TENTATIVE Billable Media

          else if TENTATIVE Billable Media >= New Weekly Budget Goals

           then =0

       

      5: Calculate New Weekly Budget Goals

           If Approve "Yes"

           Then =Original Planned Budget (DOES NOT CHANGE) + Weekly dollars to be rolled over

           Else = Original Planned Budget (DOES NOT CHANGE)

       

      6: Calculate New Weekly Imp Goals

           = New Weekly Budget Goals/CPM * 1000

       

       

      Sorry to bring up some media related metrics here, my question is what kind of logic statement/methodology I should use to get a value that may result from another logic? These metrics have a loop relationship with each other, so CASE/IF/IFF seem not working when I tried.

       

      Do you have any good idea or suggestions?

       

      Thank you so much

       

      Message was edited by: XIALIN ZHOU added a workbook sample

        • 1. Re: Loop logic functions in Tableau
          Joe Oppelt

          Post a sample workbook.  It depends on how you are compartmentalizing this.  We have a PREVIOUS_VALUE() function that lets you look back one mark and use that value.  Without a workbook I'm having a hard time juggling all the moving parts, but I think you might be able to get to the "weekly dollars to be rolled over" that way.

          • 2. Re: Loop logic functions in Tableau
            XIALIN ZHOU

            hi Joe, thank you for quickly answers! I will try this way. I also attached a sample workbook here. It will be great appreciated if you can help to check

             

            Many thanks

             

            Xialin

            • 3. Re: Loop logic functions in Tableau
              Joe Oppelt

              We need a packaged workbook so your data is available.

               

               

              This makes a twbX file instead of a twb file.


              Thanks.

              • 4. Re: Loop logic functions in Tableau
                XIALIN ZHOU

                hi Joe,

                 

                Sorry for that. the attached was updated

                 

                Let me know if you have other questions

                 

                Xialin

                • 5. Re: Loop logic functions in Tableau
                  Joe Oppelt

                  OK, I was hoping to see that you wanted to do this on a weekly basis, and that "dollars to be rolled over" was a value coming from the previous week, for example.  (Rolled over from week to week.)

                   

                  If all these calcs have to be computed for each row using only the data within the row, you can't do it because of the recursion between calcs 4 and 5, at a minimum.

                  • 6. Re: Loop logic functions in Tableau
                    XIALIN ZHOU

                    hi Joe,

                    Sorry to late respond, I was in vocation and hope you have a great holiday.

                     

                    Yes, I process this on a fiscal weekly basis. I used "lookup" function to create a metric to show value of previous week as well.

                     

                    Do you have any idea ?

                     

                    Thank you so much

                     

                    Xialin

                    • 7. Re: Loop logic functions in Tableau
                      Joe Oppelt

                      Where is the weekly dimension?  I see year, quarter and month.

                       

                      See attached.

                      • 8. Re: Loop logic functions in Tableau
                        Joe Oppelt

                        In the attached, I modified [New Weekly Budget Goals] to use the Previous_value() function.  This is what you're going to need.

                         

                        Note, it still errors out because it depends on a chain of calcs that, somewhere down the line, references [Non Billable Impressions], which doesn't exist.  And I didn't create that because your instructions above reference calcs that don't exist.

                         

                        But the key to what you need is the PREVIOUS_VALUE function.  It's the only recursive function in Tableau.

                        • 9. Re: Loop logic functions in Tableau
                          XIALIN ZHOU

                          Hi Joe,

                           

                          Thank you so much! it is really helpful. I will look into it and let you know if I have any questions!

                           

                          Appreciated it!

                           

                          Xialin

                          • 10. Re: Loop logic functions in Tableau
                            XIALIN ZHOU

                            hi Joe,

                             

                            except for this recursive relationship, there is another concern in my mind is the logic function result.

                             

                            The result of a logic function (if/iif/case) is not a single value (or calculated function), but another logic statement. does it work in Tableau?

                             

                            Thanks

                             

                            Xialin

                            • 11. Re: Loop logic functions in Tableau
                              Joe Oppelt

                              Not exactly sure what you mean.

                               

                              You can nest IF logic, even in a CASE statement.

                               

                              IF (whatever is your condition) THEN

                                 IF (some other condition) THEN  "A" ELSE "B" END

                              ELSE

                                 "C"

                              END

                               

                              CASE [Some Variable]

                              WHEN "Orange" then 1

                              WHEN "Red" then 2

                              WHEN "Blue" THEN

                                IF (some condition) THEN 3.1 ELSE 3.3 END
                              WHEN "Black" THEN 4

                              ELSE 99

                              END

                               

                              Is that what you are asking about?

                              • 12. Re: Loop logic functions in Tableau
                                XIALIN ZHOU

                                hi Joe,

                                 

                                You are right. [Non Billable Impressions] doesn't exist because it is from another data SQL source that I didn't pull into this workbook example. (I will try later) but the calculation statement for [Non Billable Impressions] look like below

                                 

                                IF [Custom SQL Query (DW_Lowes)].[Variance Authentic Viewability]<0

                                THEN 0

                                ELSEIF [Custom SQL Query (DW_Lowes)].[Variance Authentic Viewability]>=0

                                THEN SUM([Custom SQL Query (DW_Lowes)].[Authentic Impressions])*[Custom SQL Query (DW_Lowes)].[Variance Authentic Viewability]

                                END

                                 

                                My question is,  this chain of calculation contain several logic statement and depend on each other.

                                 

                                The result of a logic function (if/iif/case) is not a single value (or calculated function), but another logic statement. does it work in Tableau?

                                 

                                Thanks

                                 

                                Xialin

                                • 13. Re: Loop logic functions in Tableau
                                  Joe Oppelt

                                  Yes, you can have further calculations as the results of IF logic.

                                   

                                  In some cases I do the extra calculations in its own calc variable, and in other cases I do it right in the complex calculation itself.

                                  • 14. Re: Loop logic functions in Tableau
                                    Joe Oppelt

                                    And at times you will want to do IF logic inside a SUM() function.

                                     

                                    SUM( IF YEAR([Date] = YEAR(TODAY()) then [Sales] END )

                                     

                                    That will generate the sum of sales only for the current year.

                                     

                                    All the nesting and embedding can get really complicated, but Tableau handles it.  I just choose to compartmentalize it at times when the complexity of the calc gets overwhelming to juggle in my head.

                                     

                                    For example, I could have a calc like this:

                                     

                                    //  sales this year

                                     

                                    IF YEAR([Date] = YEAR(TODAY()) then [Sales] END


                                    And then on the sheet I would do:  SUM([sales this year])

                                     

                                    Of course that one is pretty straightforward, but you can see how compartmentalizing into multiple calcs can make a complex calc easier to read.

                                    1 2 Previous Next