9 Replies Latest reply on Feb 20, 2019 7:44 AM by Nick Parsons

    Calculating complicated payment dates

    Dan Maycock

      I have the attached workbook in which I'm trying to calculate payment terms based on a ship date.

       

      The calculation in it's current form is below

       

      If day([Ship Date]) >= 1 and day([Ship Date]) <= 9 and [Segment] = 'Home Office' then '5th Payment'

      elseif day([Ship Date]) >= 10 and day([Ship Date]) <= 19 and [Segment] = 'Home Office' then '15th Payment'

      elseif day([Ship Date]) >= 20 and [Segment] = 'Home Office' then '25th Payment'

      // if [Ship Date] was any time during prior month AND [Segment] = 'Corporate', then '10th Payment - Corp'

      // On Every friday, if [Ship Date] for was 7 days prior AND [Segment] = 'Consumer', then '[Ship Date For That Friday] - Friday Payment - Consumer'

      else 'No Payment Terms'

      END

       

      As you can see from the attached workbook, I drag this calc into the row then bring over the gross sales amount to show the amount of sales grouped by the given pay date. 

       

      I just don't have the last two lines figured out

       

      - the first line I need help with (after the //) is to show the total of sales for all ship dates in the prior month for corporate segment labeled by '10th payment - Corp'

      - the second line I need help with (after the //) is to show, each Friday, the amount of sales for the 7 prior ship dates in the week for all sales in the consumer segment labeled as '[The ship date for the given friday] - Friday Payment - Consumer'

       

      I have 4 of the 6 lines working, so just need help with the two lines above commented out for the PAYMENTTERMS calculation in the attached workbook

       

      Thank you,

       

      Dan Maycock

        • 1. Re: Calculating complicated payment dates
          Nick Parsons

          This doesn't look like the full story. This calculation is just creating text, there's no payment sum logic.  You should have some additional attributes on a supplier to identify billing period such as monthly or weekly and a calculations to figure the amount due.

           

          If you can post your workbook you'll likely get more help.

          • 2. Re: Calculating complicated payment dates
            Dan Maycock

            A workbook has been added and the question I need help with resolved. Thank you

            • 3. Re: Calculating complicated payment dates
              Nick Parsons

              Dan, thanks for the clarity and posting a twbx. Does this logic look right to you? (twbx attached)

               

              Hope this helps, if so please mark correct/helpful to help others.

               

              IF  [Segment] = 'Home Office' then

                  IF day([Ship Date]) >= 1 AND day([Ship Date]) <= 9 THEN

                      '5th Payment'

                  ELSEIF  day([Ship Date]) >= 10 AND day([Ship Date]) <= 19 THEN

                      '15th Payment'

                  ELSE

                      '25th Payment'

                  END

              ELSEIF  [Segment] = 'Corporate' THEN

                  IF DATENAME('weekday', TODAY()) = 'Friday' AND DATEDIFF('day', [Ship Date], TODAY()) = 7 THEN

                      str(day([Ship Date])) +' - Friday Payment - Consumer'

                  ELSEIF month([Ship Date]) = month(DATEADD('month', -1, today())) THEN '10th Payment - Corp'

                  ELSE

                      'No Payment Terms'

                  END

              ELSE

                  'No Payment Terms'

              END

               

              Screen Shot 2019-02-11 at 1.58.37 PM.png

              • 4. Re: Calculating complicated payment dates
                Dan Maycock

                Thanks for your help - confused here, as there are three segments in the problem but it looks like only two in your solution - segment corporation combined statements for consumer segment but wasn't broken out.

                 

                How would this look to incorporate the final statement for the consumer segment?

                 

                Per the note above

                 

                // On Every friday, if [Ship Date] for was 7 days prior AND [Segment] = 'Consumer', then '[Ship Date For That Friday] - Friday Payment - Consumer'

                • 5. Re: Calculating complicated payment dates
                  Nick Parsons

                  Oops, sorry, I misread that. Try this function then.  For consumer, I made it match your statement exactly. If it needs to be "within 7 days" vs exactly 7 days, change the operator to "<="

                   

                  IF  [Segment] = 'Home Office' then

                      IF day([Ship Date]) >= 1 AND day([Ship Date]) <= 9 THEN

                          '5th Payment'

                      ELSEIF  day([Ship Date]) >= 10 AND day([Ship Date]) <= 19 THEN

                          '15th Payment'

                      ELSE

                          '25th Payment'

                      END

                  ELSEIF [Segment] = 'Corporate' AND

                          month([Ship Date]) = month(DATEADD('month', -1, today())) THEN

                              '10th Payment - Corp'

                  ELSEIF [Segment] = 'Consumer' AND

                          DATENAME('weekday', TODAY()) = 'Friday' AND

                          DATEDIFF('day', [Ship Date], TODAY()) = 7 THEN

                              str(day([Ship Date])) +' - Friday Payment - Consumer'

                  ELSE

                      'No Payment Terms'

                  END

                  • 6. Re: Calculating complicated payment dates
                    Dan Maycock

                    I entered this into the workbook, but it looks like it's within 7 days vs just being the past 6 days (as the pay date would encompass the difference between the last pay day and this one). It should then be coming up for 3 to 4 days a month (each Friday with the corresponding date) but wasn't appearing in the list.

                    • 7. Re: Calculating complicated payment dates
                      Nick Parsons

                      [Segment] = 'Consumer' AND

                              DATENAME('weekday', TODAY()) = 'Friday' AND

                              DATEDIFF('day', [Ship Date], TODAY()) = 7

                       

                      Superstore data doesn't really have anything to test this condition well but if it's this Friday Feb 15th, any ShipDate of Feb 8th, 2019 with segment consumer would be true for this condition.

                      • 8. Re: Calculating complicated payment dates
                        Dan Maycock

                        Opening the workbook, it appears the 10th is only showing up for Januarys and no other months whereas there should be a payment on the 10th for every month as it's just the sum total of what's paid the prior month.

                         

                        What would need to get tweeked in this case?

                        • 9. Re: Calculating complicated payment dates
                          Nick Parsons

                          I used Today(), as in the day someone is running the viz, in the 'Corp' condition based on this line in your post:

                          - the first line I need help with (after the //) is to show the total of sales for all ship dates in the prior month for corporate segment labeled by '10th payment - Corp'

                           

                          So the condition

                          month([Ship Date]) = month(DATEADD('month', -1, today()))

                          will only be true for Ship Dates in January, based on the (month of Today - 1) = January.

                           

                          If you want to use another date, just substitute today() with that date field, like...

                          month([Ship Date]) = month(DATEADD('month', -1, [SOME_OTHER_DATE]))