I have a input file look like this...

ProdKey time_int Total_Unit sold_unit
A-B YTD 200 175
A-B Q3 50 45
A-B Q4 80 70
A-B 01-Nov 5 3
A-B 15-Nov 4 3

I need output report like this

GOOD UNITS

ProdKey TOTAL_YTD sold_unit_YTD Q3 Q4 01-Nov 15-Nov
A-B 200 175 90% 87.5% 60% 75%

YTD will be the sum of units but rest will be percentages sold_unit/total_unit * 100
I tried ACROSS it doesn't allow me to calculate the percentages for rest columns.

Any suggestions?

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

CRICKETMIND,

I would probably do something like this:

 
SET ASNAMES = ON
SET PRINTLIST = HOLDONLY

DEFINE FILE INPUT_FILE
TOTAL_YTD/D12.2=IF TIME_INT EQ 'YTD' THEN TOTAL_UNIT ELSE 0;
TOTAL_SOLD/D12.2=IF TIME_INT EQ 'YTD' THEN TOTAL_SOLD ELSE 0;
TABLE FILE INPUT_FILE
SUM TOTAL_YTD AS BY_TOTAL_YTD
TOTAL_SOLD AS BY_TOTAL_SOLD
BY PRODKEY
-* MULTI-VERB WILL APPLY BY_TOTAL_YTD AND BY_TOTAL_SOLD VALUES TO PRODKEY FOR ALL TIME_INT'S THIS IS IMPORTANT FOR OUR SECOND HOLD
SUM 
TOTAL_UNIT 
SOLD_UNT 
COMPUTE PERCNT/D12.2=(SOLD_UNIT/TOTAL_UNIT) * 100;
BY PRODKEY
BY TIME_INT
ON TABLE HOLD AS MY_HOLD
END

TABLE FILE MY_HOLD
SUM PERCNT
ACROSS TIME_INT 
-* BY GOING ACROSS THE TIME_INT AND HOLDING IT, THE HOLD PROCESS WILL CREATE A FIELD FOR EACH ITERATION OF TIME_INT.  
-* THIS FIELD WILL NOW HOLD THE PERCENTAGE AS ITS VALUE
BY PROD_KEY
-* BECAUSE OF THE MULTI-VERB ABOVE, YOU CAN USE A BY STATEMENT ON THE TOTAL FIELDS INSTEAD OF SUMMING THEM SINCE EACH UNIQUE VALUE OF PRODKEY ONLY HAS
-* ONE VALUE FOR EACH TOTAL FIELD.
BY BY_TOTAL_YTD AS 'TOTAL_YTD'
BY BY_TOTAL_SOLD AS 'TOTAL_SOLD'
ON TABLE HOLD AS OUTPUT_VALUES
END

-* PRINT THE RESULTS OUT ONTO THE SCREEN.  AGGREGATION AND SORTING HAS BEEN HANDLED IN THE PREVIOUS HOLD FILE
TABLE FILE OUTPUT_VALUES
PRINT *
END

There are other ways to do this. I have a fex where I use this concept to turn columns into fields and then I use a -READ to put the field names into a flat file, then use a loop to pull them out and place them where I want in the final output. This is probably more complex then what you want.

You could also do the across/hold concept above and join back to the another hold file if you don't want to use a multi-verb.

I hope some of this helps.

Use "buckets" within define statements, something like-

  SOLD_YTD/D20  = IF TIME_INT EQ 'YTD'    THEN SOLD_UNIT  ELSE 0;
  Q3/D6.1%      = IF TIME_INT EQ 'Q3'     THEN SOLD_UNIT / TOTAL_UNIT * 100  ELSE 0;

T

eric.woerle - You are a genius. I am still a novice in webfocus and had no concept of multi-verb until now. Thanks a bunch.

CRICKETMIND,

Multi-verbs took me a while to get my head around when I first started out, but they can be very powerful. I don't use them very often, but sometimes it makes things so much easier in that I don't have to create multiple hold files and join things back together.

Tony,

I was thinking along your lines originally too, but then I went with the assumption that you don't always know the time intervals the need to be defined. Maybe its dynamic, or the user can change the time frames and give it different names. Since I assumed that the actual time intervals was unknown, I had to hold the fields as an across. But If you always know the time intervals, then defining it is the simpler and better solution. One pass to report instead of my 3 passes.

You are absolutely right. My dates/time intervals are dynamic.It's rolling fork waters and rolling last four week ending dates. Another point worth mentioning - ASNAMES = ON . Thanks again.

Multi-Verb should not be required here. If everything is as described then you could use:

DEFINE FILE filename
cntr/I4        = IF ProdKey EQ LAST ProdKey THEN cntr+1 ELSE 1;
TotalYTD/I6    = IF TimeFrame EQ 'YTD' THEN TotalUnit ELSE TotalYTD; 
UnitSoldYTD/I6 = IF TimeFrame EQ 'YTD' THEN SoldUnit  ELSE UnitSoldYTD; 
D_TimeFrame/A6 MISSING ON = IF TimeFrame NE 'YTD' THEN TimeFrame ELSE MISSING;
Percent/F6.2% MISSING ON  = IF D_TimeFrame NE MISSING THEN (SoldUnit/TotalUnit)*100 ELSE MISSING; 
END
TABLE FILE filename
SUM      Percent
ACROSS   cntr NOPRINT
ACROSS   D_TimeFrame AS ''
BY       ProdKey
BY       TotalYTD
BY       UnitSoldYTD
ON TABLE SET HIDENULLACRS ON
ON TABLE SET PAGE NOLEAD
END

quote:

Originally posted by Alan B:
Multi-Verb should not be required here. If everything is as described then you could use:

DEFINE FILE filename
cntr/I4        = IF ProdKey EQ LAST ProdKey THEN cntr+1 ELSE 1;
TotalYTD/I6    = IF TimeFrame EQ 'YTD' THEN TotalUnit ELSE TotalYTD; 
UnitSoldYTD/I6 = IF TimeFrame EQ 'YTD' THEN SoldUnit  ELSE UnitSoldYTD; 
D_TimeFrame/A6 MISSING ON = IF TimeFrame NE 'YTD' THEN TimeFrame ELSE MISSING;
Percent/F6.2% MISSING ON  = IF D_TimeFrame NE MISSING THEN (SoldUnit/TotalUnit)*100 ELSE MISSING; 
END
TABLE FILE filename
SUM      Percent
ACROSS   cntr NOPRINT
ACROSS   D_TimeFrame AS ''
BY       ProdKey
BY       TotalYTD
BY       UnitSoldYTD
ON TABLE SET HIDENULLACRS ON
ON TABLE SET PAGE NOLEAD
END

I think Percent calculation should be done using compute otherwise it will change the result. Because first it will calculate the result and aggregate it and gives the percent value more than 100 which is not correct.

quote:

I think Percent calculation should be done using compute otherwise it will change the result. Because first it will calculate the result and aggregate it and gives the percent value more than 100 which is not correct.

You are correct Pravin, under general circumstances. However, this is why I said "if everything is as described". The example shows only one value line per ProdKey/TimeFrame combination, so DEFINE would be ok here.

Good point Alan!

I didn't think about using last like that. Although, I would add a pass before your request to order and aggregate everything. This again falls outside of your "If everything is as described" statement. But even if its not as described, adding a first pass would guarentee your data is in the necessary order to properly use the last command, and it would also resolve Pravin's need for a compute. Although if Pravin really wants those computes, he could still use them