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

# Calculating complicated payment dates

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

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

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

• ###### 3. Re: Calculating complicated payment dates

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

• ###### 4. Re: Calculating complicated payment dates

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

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

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

[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

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

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]))