AnsweredAssumed Answered

How to generate future occurrences from multiple inputs

Question asked by willdev on Jan 18, 2018
Latest reply on Jan 19, 2018 by Malcolm

Hello All, I am coming back to FMP, version 16, from a last experience of FMP in the late 80's and early 90's. So, I have a lot to learn. I need advice/help on a multi-dimension problem.

 

SetUp:

I have 4 data sets: (1) Week Number; (2) A Frequency Number [1 or 2 representing every other week, 3,4 or 5, representing every third week, 6,7,8 or 9 representing every 4 week, and 0 representing every week]; (3) Week Day; (4) Customer Name. The 4 different Frequency Numbers combine to make 12 different Schedule Numbers that repeat in cycles.  During 1 year the 12 part cycle will repeat 4.3 times. The 12 Schedule Numbers are: 1380, 2490, 1560, 2370, 1480, 2590, 1360, 2470, 1580, 2390, 1460, 2570.

 

Schedule Algorithm:

Week Number 1 has a Schedule Number of 1380, Week 2 is 2490, Week 3 is 1560.......Week 52 is 1480. Using MOD, I am able to generate the Schedule Number given any week number of the year:

( Mod ( ( currentWeek ) - 1 ; 2 ) + 1) &         /* Returns a 1 or 2 as first digit*/

( Mod ( ( currentWeek ) - 1 ; 3 ) + 3) &        /* Returns a 3,4 or 5 as second digit*/

( Mod ( ( currentWeek ) + 1 ; 4 ) + 6) &        /* Returns a 6,7,8 or 9 as third digit*/

0                                                                 /* Returns a 0 as fourth digit*/

In the code above, I am simply passing the Current Week value to the formula, and it returns the Schedule Number.

 

The Problem:

I would like to generate a small table, array, that shows the next 'X' occurrences, shown as a date, of a Given Customer's Frequency Number. So to get the next 6 occurences for Customer 1, their weekday is Tuesday, and their Single digit Frequency number is 8, current Week number is 1, Week Day is Monday with Schedule Number of 1380. Manually calculating the next 6 occurrences, we get: Tuesday, 2018-01-0 (1380); Tuesday, 2018-01-31 (1480); Tuesday, 2018-02-27 (1580); Tuesday, 2018-03-27 (1380); Tuesday, 2018-04-24 (1480); Tuesday, 2018-05-22 (1580).
I have not been successful in being able to generate the next 6 occurrences.SCRN- 2018-01-18 at 19.54.21.png

 

Any suggestions/advice would be greatly appreciated!  Cheers, jms

Outcomes