
1. Re: Calculating complicated payment dates
Nick Parsons Feb 11, 2019 10:10 AM (in response to Dan Maycock)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 Feb 11, 2019 12:10 PM (in response to Nick Parsons)A workbook has been added and the question I need help with resolved. Thank you

3. Re: Calculating complicated payment dates
Nick Parsons Feb 11, 2019 2:00 PM (in response to Dan Maycock)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

Example (1).twbx 431.7 KB


4. Re: Calculating complicated payment dates
Dan Maycock Feb 11, 2019 3:21 PM (in response to Nick Parsons)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 Feb 11, 2019 3:43 PM (in response to Dan Maycock)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 Feb 11, 2019 3:45 PM (in response to Nick Parsons)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 Feb 11, 2019 3:54 PM (in response to Dan Maycock)[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 Feb 12, 2019 10:39 AM (in response to Nick Parsons)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 Feb 12, 2019 12:26 PM (in response to Dan Maycock)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]))
Feel free to mark any of these responses as helpful or correct