Hi,

I am trying to pivot and get the unique PartKey count and Sales$.
By Fields are Mfr and Type
Across is YearQtr

I can use count distinct and the Pivot is fine but struggling with ROW_TOTAL.
ROW-TOTAL shows some of distinct PARTKEY and NOT distinct PARTKEY.

Below is the dataset, current result and Expected result.

Not sure how to get the expected result.
Would prefer to do this in InfoAssist but open to other suggestions


Thanks

This message has been edited. Last edited by: Kathleen Butler,

To achieve this you will have to handle the data twice, once to get your row totals and then the second to get your combined output.

Because you are using ACROSS, there will be some title issues, but I am sure there will be many suggestions on how you might achieve this.

As this is a situation that many can find an obstacle, here is some sample code using the data that you have shared. Note that the first report represents your current output, and the second is the one that you desire.

-* Start of data preparation
EX -LINES * EDAPUT MASTER,BMFILE,CV,MEM
FILE=BMFILE,SUFFIX=FOC
SEGNAME=SEG1
FIELD=MFR,      ,A1   ,A1   ,$
FIELD=YearQtr,  ,A7   ,A7   ,$
FIELD=Type,     ,A2   ,A2   ,$
FIELD=PartKey,  ,A10  ,A10  ,$
FIELD=SALES,    ,D12M ,D12  ,$
EDAPUT*
-RUN

CREATE FILE BMFILE
MODIFY FILE BMFILE
FREEFORM MFR YearQtr Type PartKey SALES
DATA
A,2015-01,1A,193,1192,$
A,2015-01,1A,1934,812,$
A,2015-01,1A,CAN00,66,$
A,2015-01,4D,CAN00,115,$
A,2015-01,4D,CAN00,1125,$
A,2015-01,1A,F06,1215,$
A,2015-02,1A,193,6556,$
A,2015-02,1A,CAN00,66,$
A,2015-02,4D,VGAS,140,$
A,2015-03,4D,1934,138,$
A,2015-03,4D,CAN,66,$
A,2015-03,3C,VJ13,640,$
A,2015-04,1A,193,3874,$
A,2015-04,1A,VJ13,1080,$
A,2015-04,2B,VJ13,558,$
B,2015-01,1A,F06,1215,$
B,2015-02,1A,193,6556,$
B,2015-02,4D,CAN,66,$
B,2015-03,3C,VJ13,64,$
B,2015-04,1A,193,38,$
B,2015-04,1A,193,64,$
END
-RUN
-* End of data preparation

SET PAGE = NOLEAD
SET ASNAMES = MIXED

TABLE FILE BMFILE
  SUM CNT.PartKey AS 'Count'
      SALES       AS 'Sum'
   BY MFR
   BY Type
ACROSS YearQtr AS ''
ON TABLE ROW-TOTAL AS 'Total'
END
-RUN

TABLE FILE BMFILE
  SUM CNT.DST.PartKey AS DSTPARTS
      SALES AS TOTSALES
   BY MFR
   BY Type
ON TABLE HOLD AS BMTOTALS
END
-RUN

JOIN CLEAR *
JOIN        FILE BMFILE   AT MFR TAG T1
  TO UNIQUE FILE BMTOTALS AT MFR TAG T2 AS J2
     WHERE T1.MFR = T2.MFR;
     WHERE T1.Type = T2.Type;
END

TABLE FILE BMFILE
  SUM MAX.T2.DSTPARTS NOPRINT
      MAX.T2.TOTSALES NOPRINT
   BY MFR
   BY Type
  SUM CNT.PartKey AS 'Count'
      SALES       AS 'Sum'
   BY MFR
   BY Type
ACROSS YearQtr AS ''
COMPUTE TotCount/I9 = C1; AS 'Count'
COMPUTE TotSales/D12M = C2; AS 'Sum'
END
-RUN

T

Forgot to mention that the reason you would need to do two passes is that you cannot have a distinct operator in the first section of a multi-verb request.

T

awesome!!
thanks so much. works perfectly.

I guess there is not drag and drop way for infoassist analysts.