Well, let me try to explain what I need to create:

Let's say that this report is from 2015, JULY

DAY..................MODEL X.......MODEL Y....MODEL Z
01......................10.................11...............15
02......................12..................5................8
03........................8..................5................4
AVG.....................10..................7................9
06........................2..................8...............11
07........................4..................7...............12
08........................3..................9...............10
AVG.......................3..................8...............11
VS LAST WEEK..-70%...............60%..............22%



The column DAY is the day of the month. "Models" are ACROSS column with summed volumes.

The row "AVG" is a total, containing the AVERAGE of each July's week volumes.

The row "VS LAST WEEK" is what I need. It should compare the AVG total against the last AVG total and shows the percentage of growing (or decreasing).

Is it possible to do that ? How ?



Thanks !

This message has been edited. Last edited by: <Kathryn Henning>,

Yes it is, using something like FML or multi pass and possibly another couple of ways.

The simplest (IMHO) would be to use FML. Consider this example using GGSALES -

DEFINE FILE GGSALES
  MTHYEAR/MYY = DATE;
END
TABLE FILE GGSALES
   SUM DOLLARS/D8c AS ''
ACROSS PRODUCT AS ''
FOR MTHYEAR
'01/1996'          LABEL LAB1 OVER
'02/1996'          LABEL LAB2 OVER
'03/1996'          LABEL LAB3 OVER
''        AS 'AVG' LABEL AVG1 OVER
RECAP AVG1 = (LAB1 + LAB2 + LAB3) / 3; OVER
'04/1996'          LABEL LAB4 OVER
'05/1996'          LABEL LAB5 OVER
'06/1996'          LABEL LAB6 OVER
'' AS 'AVG' LABEL AVG2 OVER
RECAP AVG2 = (LAB4 + LAB5 + LAB6) / 3; OVER
'' AS 'VS Last Qtr' LABEL LABVS OVER
RECAP LABVS/D8 = (AVG2 - AVG1) / AVG1 * 100;
ON TABLE SET PAGE TOP
ON TABLE SET STYLE *
  type=data, label=AVG1, backcolor=silver, $
  type=data, label=AVG2, backcolor=silver, $
  type=data, label=LABVS, backcolor=blue, color=white, $
END
-RUN

If you check out the documentation for FML ( here) you will learn how useful it can be.

There are also countless posts on the subject in the Forum.


T

Nakiri,
Here is a possibility. I'm using the CAR file which does not have the data you need.
In the example your Model data is represented by the field SEATS, the volumes are the RCOST field. Days are the ranking of the MPG field. Since you show 3 days per week, the same is used here.

  
-* File Nakiri01.fex
TABLE FILE CAR
SUM RCOST AS MODEL
ACROSS SEATS 
RANKED AS DAY BY MPG IN-GROUPS-OF 2
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END
-RUN
DEFINE FILE HOLD
WEEK/I2=1 + INT((DAY-1)/3);
END
SET PRINTPLUS=ON
TABLE FILE HOLD
SUM AVE.MODEL2 NOPRINT 
    AVE.MODEL4 NOPRINT 
    AVE.MODEL5 NOPRINT
COMPUTE AVG2/D6.2%S=IF LAST WEEK EQ 0 THEN 0 ELSE IF WEEK EQ LAST WEEK THEN 0 ELSE C1 / LAST C1 * 100; NOPRINT
COMPUTE AVG4/D6.2%S=IF LAST WEEK EQ 0 THEN 0 ELSE IF WEEK EQ LAST WEEK THEN 0 ELSE C2 / LAST C2 * 100; NOPRINT
COMPUTE AVG5/D6.2%S=IF LAST WEEK EQ 0 THEN 0 ELSE IF WEEK EQ LAST WEEK THEN 0 ELSE C3 / LAST C3 * 100; NOPRINT

BY WEEK NOPRINT
ON WEEK SUBFOOT
"Vs. Last Week <AVG2<AVG4<AVG5"
WHEN AVG2 NE 0;

PRINT MODEL2 MODEL4 MODEL5
BY WEEK
BY DAY
ON WEEK SUBTOTAL AVE. AS AVG
ON TABLE SET HTMLCSS ON
ON TABLE NOTOTAL
ON TABLE SET STYLE *
 INCLUDE=ENDEFLT, $
TYPE=SUBFOOT, HEADALIGN=BODY,$
END

Hi Tony,

First of all, thanks again ! It will help me in the next report !

But using this method here, I would have to dinamically create this "FOR/OVER" structure, right ? Because there are 1 to 28/29/30 or 31 days (depending on month) and the AVG row position will be changing according with the selected month too, right ?

I said this report would be about 2015/July. But if it was about 2015/August, the AVG row would not be between days 3 and 4, but between days 8 and 9 (or between days 1 and 2).

So, is there a way to create this layout dinamically, or am I getting it wrong ?



Thank you !

Danny,

Actually the days per week will be according to the month. (I was just lazy to write all days down... lol)

For 2015/July:
Days 1 to 4 - 1st week
AVG
Days 5 to 11 - 2nd week
AVG and VS
Days 12 to 18 - 3rd week
AVG and VS
Days 19 to 25 - 4th week
AVG and VS
Days 26 to 31 - 5th week
AVG and VS


But if it was 2015/Feb:
For July:
Days 1 to 7 - 1st week
AVG
Days 8 to 14 - 2nd week
AVG and VS
Days 15 to 21 - 3rd week
AVG and VS
Days 22 to 28 - 4th week
AVG and VS
and there's no 5th week


I'm reading/learning with your example. Get back to you as soon as I get it...


Thank you !

quote:

I would have to dinamically create this "FOR/OVER" structure, right ?

There's always a downside but if you get a handle on the actual syntax that you need, it's just a case of writing the DM code to satisfy your requirement.

.. and of course, there are other methods that may appeal!


T

As I mentioned in my first reply, you could always use multi pass but then you'd have to accept the potential inefficiencies of handling data multiple times.

Take this code. It produces exactly the same HTML output as my previous example, but it handles 3 * 1076 records plus 1 * 90 records, whereas the first example only handles 1076 records once. If you expand that to the number of records in your potential extract then you can see why I would be in favour of FML!

FILEDEF TEMPDAT1 DISK TEMPDAT1.FTM (APPEND
-RUN
SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
SET PAGE     = TOP

DEFINE FILE GGSALES
  QTRYEAR/A12   = FPRINT(DATECVT(DATE, 'I8YYMD', 'YYQ'), 'QYY', 'A12');
  MTHYEAR/A12   = FPRINT(DATECVT(DATE, 'I8YYMD', 'YYM'), 'MYY', 'A12');
  DUMMY1/A12    = 'AVG';
  DUMMY2/A12    = 'VS Last Qtr';
  MNTHYEAR/MYY  = DATE;
  Q1_DOLLARS/I8 = IF MNTHYEAR FROM '01/1996' TO '03/1996' THEN DOLLARS ELSE 0;
  Q2_DOLLARS/I8 = IF MNTHYEAR FROM '04/1996' TO '06/1996' THEN DOLLARS ELSE 0;
END

TABLE FILE GGSALES
  SUM DOLLARS/D8c
      COMPUTE SORT_ORD/I3 = 1; 
      COMPUTE FORMAT/A8   = 'I8';
   BY QTRYEAR
   BY MTHYEAR
   BY PRODUCT
WHERE MNTHYEAR FROM '01/1996' TO '06/1996'
ON TABLE HOLD AS TEMPDAT1 FORMAT ALPHA
END
-RUN

TABLE FILE GGSALES
  SUM COMPUTE AVE_DOLLARS/D8c = DOLLARS / 3; AS DOLLARS
      COMPUTE SORT_ORD/I3 = 2; 
      COMPUTE FORMAT/A8   = 'I8';
   BY QTRYEAR
   BY DUMMY1  AS MTHYEAR
   BY PRODUCT
WHERE MNTHYEAR FROM '01/1996' TO '06/1996'
ON TABLE HOLD AS TEMPDAT1 FORMAT ALPHA
END
-RUN

TABLE FILE GGSALES
  SUM COMPUTE AVE_DOLLARS/D8c = (Q2_DOLLARS - Q1_DOLLARS) / Q1_DOLLARS * 100; AS DOLLARS
      COMPUTE SORT_ORD/I3 = 3; 
   BY DUMMY2  AS QTRYEAR
   BY DUMMY2  AS MTHYEAR
   BY PRODUCT
WHERE MNTHYEAR FROM '01/1996' TO '06/1996'
ON TABLE HOLD AS TEMPDAT1 FORMAT ALPHA
END
-RUN

TABLE FILE TEMPDAT1
  SUM DOLLARS
   BY QTRYEAR  NOPRINT
   BY SORT_ORD NOPRINT
   BY MTHYEAR  AS ''
ACROSS PRODUCT AS ''
ON TABLE SET PAGE TOP
ON TABLE SET STYLE *
  type=data, backcolor=silver, when=MTHYEAR EQ 'AVG', $
  type=data, backcolor=blue, color=white, when=MTHYEAR EQ 'VS Last Qtr', $
ENDSTYLE
END

T

@ Danny,

Nice alternative I tried your suggestion but used a MacGyver technique to produce an entire month (using DateTime dates to get Week No) and it works perfectly including the percentage formatting.

Apart from the subtotal title over problem that has existed since .....

T

@ Tony,
Thanks.

Hi !

I'll say that Dannys's options will work best for me. I had to stop with this report to make an update into another one, but I think it will do the trick.

Tony, do you have the example of using MacGyver technique ?



Thank you, guys !