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

    IF statement help

    Mikey Michaels

      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
          Ivan Young

          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
            Mikey Michaels

            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
              Ivan Young

              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
                Mikey Michaels

                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
                  Ivan Young

                  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
                    Mikey Michaels

                    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
                      Ivan Young

                      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 InvoiceDate DiffDocument DateDocument TypeNet due dateCurrentTotal Balance
                      4061297DR89065188644/11/2016
                        0:00
                      RV6/14/2016
                        0:00
                      131,359.95131,359.95
                      4061297DR89065411655/11/2016
                        0:00
                      RV7/15/2016
                        0:00
                      1,200.951,200.95
                      4061297DR89065424655/11/2016
                        0:00
                      RV7/15/2016
                        0:00
                      4,590.384,590.38
                      4061297DR89065431655/11/2016
                        0:00
                      RV7/15/2016
                        0:00
                      7,005.587,005.58
                      4061297DR89065155684/7/2016
                        0:00
                      RV6/14/2016
                        0:00
                      73,909.8273,909.82
                      4061297DR89065156684/7/2016
                        0:00
                      RV6/14/2016
                        0:00
                      18,281.8818,281.88
                      236,348.56
                      • 8. Re: IF statement help
                        Mikey Michaels

                        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!