8 Replies Latest reply on Jun 23, 2016 12:51 PM by Mikey Michaels

# IF statement help

Hello All,

I'm looking for some much needed help.

What I'm trying to write is a calculated field that does the following:

• IF sum ([CURRENT]) is greater than 10,000 and the  [DATE DIFF] is > 60  AT THE CUSTOMER INVOICE LEVEL THEN SUM( CURRENT)

From my very manual calculations, below are the invoices that meet the specified criteria:

Any ideas on how to solve?

I have provided a sample workbook on the attached.

Cheers,

Mikey

• ###### 1. Re: IF statement help

Hi Mikey,

I think your problem is trying to use SUM.  Just create a non-aggregated calculation to use as your measure and filter and you are all good.

Calculated field [Biggest Deadbeats] = IF [Current] > 10000 and [Date Diff] > 60  then [Current] END

Use the field as your measure and also as a filter.  Set the filter value to at least.1

Let me know if you have any questions.

Regards,

Ivan

1 of 1 people found this helpful
• ###### 2. Re: IF statement help

Thank you so much, Ivan. I'm very new to Tableau and appreciate your help!

Quick follow up question-

I would like to adjust the current view to show only the Credit Account, average Date diff of the 3 invs that met the IF statement criteria(68+68+64 / 3), and the total of [Current] that met the criteria, which would be 224K.

Thanks for your time!

Cheers.

Mikey

• ###### 3. Re: IF statement help

It's my pleasure Mikey.  I think you should be able to remove customer invoice from rows and change Date Diff from SUM to AVG.

1 of 1 people found this helpful
• ###### 4. Re: IF statement help

Ah doh! I was able to figure out getting the sum of the [CURRENT] field correct, however I'm struggling with the AVG of [Date Diff].

You see, I only want the average of [Date Diff] if all the conditions of [Biggest Deadbeats] are met -> Love the name btw!

So, I tried to mirror the [Biggest Deadbeats] field by typing the following:

IF ([Current]) > 10000 and ([Date Diff]) > 60  then AVG([Date Diff]) END

Now I get the dreaded "cannot mix agg...blah, blah

Anyways, the average I want to show should be 66.67 (68+68+64 / 3)

Whew, hopefully you understand...THANK YOU SO MUCH FOR YOUR ASSISTANCE!

• ###### 5. Re: IF statement help

Hi Mikey,

You are overcomplicating, the calculated field does not need to be changed at all.  We currently have a non-aggregate measure for date diff which basically means it is being calculated on each row.  All you need to do is look at the average for this measure instead of the sum.  See the screenshot below for how to change from SUM to AVG.  After you have made this change remove Customer Invoice from rows and you should be all set.  Let me know if it's not clear.

Regards,

Ivan

1 of 1 people found this helpful
• ###### 6. Re: IF statement help

Ah I figured that out and forgot to reply that my question was solved.

Thank you so much for your time...I have learned so much from this thread!

I do, however, have one more question

The formula you created [Biggest Deadbeats] = IF [Current] > 10000 and [Date Diff] > 60  then [Current] END

works wonderfully, however I have a unique situation on the attached that has occurred on some credit accounts.

The Credit Account "BT" shows up as "null". I think I know what is happening but I do not know how to adjust the [Biggest Deadbeats] formula. The [Current] total for "BT", if you add up all line items, is > 10K. I guess the reason why it is not included is because each line item is <10K?

Finally, my question: how can I adjust the [Biggest Deadbeats] to include  this Credit Account?

Again, thank you for your time!

• ###### 7. Re: IF statement help

Hi Mikey,

You are correct as to why they aren't included.  The current formula is looking at each row of data, the date diff for that row and the amount on each row.  Can you provide more detail as to how this situation is unique?  Are all the lines in the excel example past 60?  Why would you want all of these included and not the rows I have highlighted below?  You may want to rethink the inclusion criteria for this report as it's more at the Account Name level rather than invoice.

Regards,
Ivan

 Credit  Account Credit Account  Name Customer Invoice Date Diff Document Date Document Type Net due date Current Total Balance 4061297 DR 89065188 64 4/11/2016  0:00 RV 6/14/2016  0:00 131,359.95 131,359.95 4061297 DR 89065411 65 5/11/2016  0:00 RV 7/15/2016  0:00 1,200.95 1,200.95 4061297 DR 89065424 65 5/11/2016  0:00 RV 7/15/2016  0:00 4,590.38 4,590.38 4061297 DR 89065431 65 5/11/2016  0:00 RV 7/15/2016  0:00 7,005.58 7,005.58 4061297 DR 89065155 68 4/7/2016  0:00 RV 6/14/2016  0:00 73,909.82 73,909.82 4061297 DR 89065156 68 4/7/2016  0:00 RV 6/14/2016  0:00 18,281.88 18,281.88 236,348.56
• ###### 8. Re: IF statement help

Hi Ivan,

I guess what I'm trying to say is that I want the formula to aggregate the current column at the invoice level first, then see if that total is >10K. In my dataset which I have attached, I would "BT" to be included as the sum of the current column by invoice level is >10K even though no line item is >10k. Also, the Date Diff calculation is >60 Days for each line item. See the attached.

Thanks for your help!