Hello,

The user wants the QTY Rem /7Wk. He gave me the below excel spread sheet. I appreciate your help this issue.


Qty Rem 6/4/2017 6/11/2017 6/18/2017 6/25/2017 7/2/2017 7/9/2017 7/16/2017

6/4/2017 20 2 2 2 2 2 2 8

6/11/2017 16 2 2 2 2 2 6

6/18/2017 12 2 2 2 2 4

6/25/2017 9 2 2 2 3

7/2/2017 5 1 1 3

7/9/2017 2 1 1

7/16/2017

Thank you

This message has been edited. Last edited by: FP Mod Chuck,

Could you please explain what you're asking? I've no idea what you're trying to do.

baba,

Example :
QTYREM = 20 I have to take the 20 items and divide the qtyrem by 7wk. Please see the example below qtyrem=20 ,I have to place 2 items each week and 7th week it should show remaining. Please let me know if you need more details.

example date= 6/4/2017 6/11/2017 6/18/2017 6/25/2017 7/2/2017 7/9/2017 7/16/2017
QTYREM = 20 2 2 2 2 2 2 8


Thank you

Erm, is that "quantity remaining" as a weekly inventory recap for 7 weeks descending? So if the top right column B is "Quantity Remaining"... errrm... so what happens in Column C-I then? I can't figure out what is happening there except the sum is on column B but what do the dates in X and Y axis correlate with? So if 6/4/2017 we have 20 and 6/4/2017 also 2 happens? If thats sales, then how do you get to 16?

Clear as mud, that.

So, once the logic of what the numbers represent is clear, then the question is what does the database contain and is there any sales or inventory aggregates. Or is that just an "I want" and no idea of what is in the database.

Then we get into how do we calculate the "week" and what does it correlate to and how often the numbers change and what do you do when the year changes and such small details.

OK, so in that original table do the rows correlate with each other or not?

Or is the number given there in the first column just a number given and then you have to spread the "usage" as in how long will it stretch? Then by the example given the 2nd row makes no sense, as by the logic given it would be
6/11/2017 16 2 2 2 2 2 2 4

As for the remaining of a division you can use :

DEFINE FILE CAR
NBWK     /P2 = 7;
QTY      /P2 = 16;
QTYPERWK /P2 = INT(QTY / NBWK);
QTY7WK   /P2 = QTYPERWK * NBWK;
QTYREM   /P2 = DMOD(QTY, NBWK, QTYREM);
QTY7THWK /P2 = QTYPERWK + QTYREM;
END
TABLE FILE CAR
PRINT QTY      AS 'Starting Qty'
      QTYPERWK AS 'Qty Per Week'
      QTY7WK   AS 'Qty After,7 weeks'
      QTYREM   AS 'Remaining Qty,After 7 weeks'
      QTY7THWK AS 'Qty Of The,7th week'
BY COUNTRY NOPRINT
WHERE READLIMIT   EQ 1;
WHERE RECORDLIMIT EQ 1;
END

So after 7 weeks of 2 units per week, it will remain 4 units resulting in :
Wk1 = 2
Wk2 = 2
Wk3 = 2
Wk4 = 2
Wk5 = 2
Wk6 = 2
Wk7 = 4

quote:

Then by the example given the 2nd row makes no sense

Not only the second : from the second line it doesn't make sense

This message has been edited. Last edited by: MartinY,

And here a way to assign Qty per week.
But keep in mind that it's based on assumption that it's evenly distributed against the 7 weeks and where the latest week has the remaining qty added to its week value:

DEFINE FILE CAR
NBWK     /P2 = 7;
QTY      /P2 = 5;
QTYPERWK /P2 = INT(QTY / NBWK);
QTY7WK   /P2 = QTYPERWK * NBWK;
QTYREM   /P2 = DMOD(QTY, NBWK, QTYREM);
QTY1     /P2 = IF QTY LE 7 AND QTY GE 1 THEN 1 ELSE QTYPERWK;
QTY2     /P2 = IF QTY LE 7 AND QTY GE 2 THEN 1 ELSE QTYPERWK;
QTY3     /P2 = IF QTY LE 7 AND QTY GE 3 THEN 1 ELSE QTYPERWK;
QTY4     /P2 = IF QTY LE 7 AND QTY GE 4 THEN 1 ELSE QTYPERWK;
QTY5     /P2 = IF QTY LE 7 AND QTY GE 5 THEN 1 ELSE QTYPERWK;
QTY6     /P2 = IF QTY LE 7 AND QTY GE 6 THEN 1 ELSE QTYPERWK;
QTY7     /P2 = IF QTY EQ 7              THEN 1 ELSE (IF QTYPERWK GT 0 THEN QTYPERWK + QTYREM ELSE 0);

END
TABLE FILE CAR
PRINT QTY      AS 'Starting Qty'
      QTYPERWK AS 'Qty Per Week'
      QTY7WK   AS 'Qty After,7 weeks'
      QTYREM   AS 'Remaining Qty,After 7 weeks'
      QTY1     AS 'Qty Wk 1'
      QTY2     AS 'Qty Wk 2'
      QTY3     AS 'Qty Wk 3'
      QTY4     AS 'Qty Wk 4'
      QTY5     AS 'Qty Wk 5'
      QTY6     AS 'Qty Wk 6'
      QTY7     AS 'Qty Wk 7'
BY COUNTRY NOPRINT
WHERE READLIMIT   EQ 1;
WHERE RECORDLIMIT EQ 1;
END

This message has been edited. Last edited by: MartinY,

Does the following this:

6/4/2017   20  2  2  2  2  2  2  8

Mean that this is also true

6/18/2017  12  2  2  2  2  4  .  . ?

Where the periods are missing data? Or should they be zeros?

Thank you all reply.
Thank you MartinY, I used your code and modified littlebit .

 DEFINE FILE CAR
WK7      /P2 = 7;
STQTY7   /P2 = 20;
QTYPERWK7 /P2 = INT(STQTY7 / WK7);
QTYREM7   /P2 = DMOD(STQTY7, WK7, QTYREM7);
QTY1_7   /P2 =  QTYPERWK7;
QTY2_7   /P2 =  QTYPERWK7;
QTY3_7   /P2 =  QTYPERWK7;
QTY4_7   /P2 =  QTYPERWK7;
QTY5_7   /P2 =  QTYPERWK7;
QTY6_7   /P2 =  QTYPERWK7;
QTY7_7   /P2 =  (QTYPERWK7 + QTYREM7);


WK6       /P2 = 6;
STQTY6    /P2 = QTY2_7 + QTY3_7 + QTY4_7  + QTY5_7 + QTY6_7 + (QTY7_7 - QTY1_7 );
QTYPERWK6 /P2 = INT(STQTY6 / WK6);
QTYREM6  /P2 = DMOD(STQTY6, WK6, QTYREM6);
QTY1_6   /P2 =  QTYPERWK6 - QTYPERWK6 ;
QTY2_6   /P2 =  QTYPERWK6;
QTY3_6   /P2 =  QTYPERWK6;
QTY4_6   /P2 =  QTYPERWK6;
QTY5_6   /P2 =  QTYPERWK6;
QTY6_6   /P2 =  QTYPERWK6;
QTY7_6   /P2 =  (QTYPERWK6 + QTYREM6);


WK5       /P2 = 5;
STQTY5    /P2 = QTY3_6 + QTY4_6  + QTY5_6 + QTY6_6 + (QTY7_6 - (QTY1_6 + QTY2_6 ) );
QTYPERWK5 /P2 = INT(STQTY5/ WK5);
QTYREM5  /P2 = DMOD(STQTY5, WK5, QTYREM5);
QTY1_5   /P2 =  QTYPERWK5 - QTYPERWK5 ;
QTY2_5   /P2 =  QTYPERWK5 - QTYPERWK5 ;
QTY3_5   /P2 =  QTYPERWK5;
QTY4_5   /P2 =  QTYPERWK5;
QTY5_5   /P2 =  QTYPERWK5;
QTY6_5   /P2 =  QTYPERWK5;
QTY7_5   /P2 =  (QTYPERWK5 + QTYREM5);

END


TABLE FILE CAR
PRINT
      STQTY7     AS 'Starting Qty'
      QTY1_7     AS 'Qty Wk 1'
      QTY2_7     AS 'Qty Wk 2'
      QTY3_7     AS 'Qty Wk 3'
      QTY4_7     AS 'Qty Wk 4'
      QTY5_7     AS 'Qty Wk 5'
      QTY6_7     AS 'Qty Wk 6'
      QTY7_7     AS 'Qty Wk 7'

BY COUNTRY NOPRINT
WHERE READLIMIT   EQ 1;
WHERE RECORDLIMIT EQ 1;
END

TABLE FILE CAR
PRINT

      STQTY6     AS 'Starting Qty'
      QTY1_6     AS 'Qty Wk 1'
      QTY2_6     AS 'Qty Wk 2'
      QTY3_6     AS 'Qty Wk 3'
      QTY4_6     AS 'Qty Wk 4'
      QTY5_6     AS 'Qty Wk 5'
      QTY6_6     AS 'Qty Wk 6'
      QTY7_6     AS 'Qty Wk 7'

BY COUNTRY NOPRINT
WHERE READLIMIT   EQ 1;
WHERE RECORDLIMIT EQ 1;
END

TABLE FILE CAR
PRINT

      STQTY5    AS 'Starting Qty'
      QTY1_5    AS 'Qty Wk 1'
      QTY2_5    AS 'Qty Wk 2'
      QTY3_5    AS 'Qty Wk 3'
      QTY4_5    AS 'Qty Wk 4'
      QTY5_5    AS 'Qty Wk 5'
      QTY6_5    AS 'Qty Wk 6'
      QTY7_5    AS 'Qty Wk 7'

BY COUNTRY NOPRINT
WHERE READLIMIT   EQ 1;
WHERE RECORDLIMIT EQ 1;
END


-EXIT