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

Loop logic functions in Tableau

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

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

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

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

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

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

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

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

See attached.

• 8. Re: Loop logic functions in Tableau

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

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

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

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

• 12. Re: Loop logic functions in Tableau

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

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

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