Hello everyone.

I've probably overlooked the solution somewhere on this forum. For that, I apologize. However, I'm trying to create a custom row total.

I have seven columns in my actual report, and I only want to total six of them. I found some documentation about CNOTATION. I thought it might be helpful; however, it seems to create a running total for each column. Below is some sample code I worked with in one of the demo files. When it's run, you'll see what I mean.

SET CNOTATION = ALL


TABLE FILE SALES
SUM
     SALES.PRODUCT.RETAIL_PRICE
AND COMPUTE SSPRICE=C1+C2;
BY  SALES.STOR_SEG.STORE_CODE
ACROSS LOWEST SALES.PRODUCT.PROD_CODE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
$
TYPE=TITLE,
     ACROSSCOLUMN=N1,
$
ENDSTYLE
END  

What I really need is for each prod code EXCEPT product code E3 to be computed into a total.

Can someone give me a gentle hint or point me in the right direction as to what I might be missing?

By way of example, here's another take on what I want to do:

If I have seven categories running across the top of table, I only want to total the first six columns.

Thank you in advance as always for your help or guidance.

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

Search the forum for the "SEQUENCE" keyword.

It might not be straight forward with TITLE positioning though

T

Thank you, Tony, for that.

Upon further reflection,I am not sure the CNOTATION is my preferred method after all.

Basically, in plain language what I'm trying to do is the following:

I have seven columns of data.

Perhaps, the columns are: Apples, Bananas, Oranges, Strawberries, Blueberries, Strawberries and Kiwi.

My BY field is the year.

My verb request is SUM with RPCT (row percentage) on it.

I want the total percentage to calculate the percentage for each column.

However, when I display the table, I don't want to display KIWI, for example.

If I eliminate it through a WHERE statement, of course, the percentages of the other fruits will get recalculated.

Any other advice?

If I understand your scenario correctly:
* Matrix report
* Row % Calculations
* Hide one set of columns in the matrix at display time.

Sounds like a combination of the HIDENULLACRS setting and WHERE TOTAL might be able to help.

SET HIDENULLACRS=ON
-*
DEFINE FILE GGSALES
 YEAR/YY = IF SEQ_NO LT 2500 THEN 2019 ELSE 2020;
 MYDOLLARS/D12.2C = DOLLARS;
END
-*
TABLE FILE GGSALES
"Total/Row % Sales Across Region By Year"
"Hiding Midwest Region"
SUM MYDOLLARS
    RPCT.MYDOLLARS
ACROSS REGION
BY YEAR
WHERE TOTAL REGION NE 'Midwest'
ON TABLE SET STYLE *
  INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/jellybean_combo.sty, $
ENDSTYLE
END  

Step 1
Extract and perform your total value for all fruits (including Kiwi) by Year.
Should end up with one record per Year with two columns : Year & TotalValue

Step 2
Extract detailed data per Year & Fruits EXCEPT Kiwi (WHERE Fruit NE 'Kiwi')
Should end up with one record per Year/Fruit with three columns : Year, Fruit & DetTotValue

Step 3
Join Total file (step 1) with detailed one (step 2)
In other words you should end up with a files having as many rows as you have in detailed file but where TotalValue will be added as a new column for each Year/Fruits rows
Should end up with one record per Year/Fruit with FOUR columns : Year, Fruit, DetTotValue, TotalValue

Step 4
Use Step 3 file to perform your percentage of Total

SUM COMPUTE Pct/P8.2C% = DetTolValue / TotalValue * 100;
BY Year
ACROSS Fruit

Or perform something similar to this

Thank you, David, for that answer.

Perhaps I am not communicating clearly enough. (Sometimes communication can be the main challenge here on the forum. LOL.)

Your solution is very close, and I have gotten that part. Here's my true question though:

If I use a ROW-TOTAL at this point, will I still get 100% for a total? I don't want 100%.

I want 76.7%, the value of the three regions you've displayed in the final report as my total rate for the regions. Does that make sense?

Other than that, I think the solution is great, and it's pretty much what I had. So, it's wonderful knowing I understood more than I thought.

MartinY,

Your method makes sense, and I was trying something similar. Evidently, I might have missed a step though. Before I start down this path again, my question is will step 3 result in the true percentage? That is, if three fruits accounted for 76.2% of the total and the final fruit (Kiwi) accounted for 23.8%, will step 3 result in 76.2% instead of 100%. I want the 76.2%. If I understand your solution correctly, I think it should. Am I correct in that assessment?

No percentage calculation at Step 3, only summarize the value by Year/Fruit to then be able to perform the percentage

In other word, you may have several sales rows per Year and Fruit but what you are interested by it's the total sales per Year/Fruit

So in Step 1 you will end up with
Yr 2017 = 1000 $ of Total sales
Yr 2018 = 2000 $ of Total sales
Yr 2019 = 1500 $ of Total sales

Step 2
Yr 2017 Apples = 300 $ of sales
Yr 2017 Bananas = 500 $ of sales
Yr 2017 Kiwi = 200 $ of sales -- but this record is omitted by the where clause
Yr 2018 Apples = 1300 $ of sales
Yr 2018 Bananas = 200 $ of sales
Yr 2018 Kiwi = 500 $ of sales -- but this record is omitted by the where clause
Yr 2019 Apples = 800 $ of sales
Yr 2019 Bananas = 600 $ of sales
Yr 2019 Kiwi = 100 $ of sales -- but this record is omitted by the where clause

Step 3
Yr 2017 Apples = 300 $ of sales, TotalSales 1000 $
Yr 2017 Bananas = 500 $ of sales, TotalSales 1000 $
Yr 2018 Apples = 1300 $ of sales, TotalSales 2000 $
Yr 2018 Bananas = 200 $ of sales, TotalSales 2000 $
Yr 2019 Apples = 800 $ of sales, TotalSales 1500 $
Yr 2019 Bananas = 600 $ of sales, TotalSales 1500 $

Step 4
Yr 2017 Apples Pct = 300 / 1000 * 100
Yr 2017 Bananas Pct = 500 / 1000 * 100
Yr 2018 Apples Pct = 1300 / 2000 * 100
Yr 2018 Bananas Pct = 200 / 2000 * 100
Yr 2019 Apples Pct = 800 / 1500 * 100
Yr 2019 Bananas Pct = 600 / 1500 * 100

quote:

...If I use a ROW-TOTAL at this point, will I still get 100% for a total? I don't want 100%.

I want 76.7%, the value of the three regions you've displayed in the final report as my total rate for the regions...

Oh, didn't see the need for a row-total of the 'non-hidden' percentages.

Here is a variation of my previous model..adding a check report to support data checking during development..

DEFINE FILE GGSALES
 YEAR/YY = IF SEQ_NO LT 2500 THEN 2019 ELSE 2020;
 MYDOLLARS/D12.2C = DOLLARS;
END
-*
TABLE FILE GGSALES
SUM    RPCT.MYDOLLARS/D12.2% AS 'RPCT'
ACROSS REGION 
BY     YEAR
WHERE TOTAL REGION NE 'Midwest';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT FOCUS
END
-RUN
-*
TABLE FILE HOLD
"Total/Row % Sales Across Region By Year"
"Hiding Midwest Region"
PRINT YEAR           AS 'Year'
      RPCTNortheast  AS 'Northeast'
	  RPCTSoutheast  AS 'Southeast'
	  RPCTWest       AS 'West'	                       
COMPUTE ROW_TOTAL/D12.2% = RPCTNortheast + RPCTSoutheast + RPCTWest; AS 'Row,Total'
ON TABLE SET STYLE *
  INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/jellybean_combo.sty, $
ENDSTYLE
END
-*
TABLE FILE GGSALES
"CHECK"
SUM    RPCT.MYDOLLARS AS 'RPCT'
ACROSS REGION ACROSS-TOTAL 
BY     YEAR              
ON TABLE SET STYLE *
  INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/jellybean_combo.sty, $
ENDSTYLE
END  

For sure, with the power and flexibility of the language, there are probably several approaches. :-) Have fun!

Forgive me if I'm taking this to the Top/Initial Post. But, is the real question (based on: "I have seven columns in my actual report, and I only want to total six of them"): How do I only show totals for 6 of the 7 columns? If so, Answer: ON {something} SUBTOTAL COL1 COL2 COL3 COL4 COL5 COL7

I think this is what Julie requires, but there's an issue with column titles.

An alternative might be to create a separate bucket for each across value.

DEFINE FILE GGSALES
  PARTIAL_ROW/D12c = IF PRODUCT EQ 'Capuccino' THEN 0 ELSE DOLLARS;
END

TABLE FILE GGSALES
-* Firstly aggregate values at STCD level
SUM PARTIAL_ROW
-* Uncomment the next line to enable comparison between partial and full row-totals
-*	DOLLARS AS 'ROW-TOTAL'
BY STCD
-* Secondly aggregate your required content
SUM DOLLARS
BY STCD
ACROSS PRODUCT AS ''
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
-* Thirdly, place the first "non-BY" report column in the final column position
TYPE=REPORT, COLUMN=C1, SEQUENCE=14, $
ENDSTYLE
END
-RUN

T

JulieA said:

quote:

I have seven columns in my actual report, and I only want to total six of them.

I understand that Julie wants to show all the columns but sum only 6.
Using David's example and the column-notation:

  
-SET &ECHO=ALL;
-* File juliea01.fex
DEFINE FILE GGSALES
 YEAR/YY = IF SEQ_NO LT 2500 THEN 2019 ELSE 2020;
 MYDOLLARS/D6.2% = DOLLARS;
 DOLLARS/D8 = DOLLARS;
END
-*
TABLE FILE GGSALES
"Total/Row % Sales Across Region By Year"
"Excluding Midwest Region from the Total"
SUM DOLLARS
    RPCT.MYDOLLARS
ACROSS REGION COMPUTE PCTROWTOTAL/D6.2%=C4 + C6 + C8; AS 'PCT TOTAL'
BY YEAR
ON TABLE SET STYLE *
  INCLUDE = endeflt, $
ENDSTYLE
END  

Everyone,

Oh my goodness! There are so many wonderful ideas and solutions, and I am extremely grateful to all of you who posted replies to my question.

In the end, I selected David Briars' approach. My supervisor liked it very much.

As always, thank you for sharing your expertise with me. I've kept notes on all these techniques. You never know when they might come in handy on another project.

Everyone,

While the solution David Briars provided works great and I appreciate the help, we'd like to add one more component-a where clause to eliminate records where the seventh column (RPCT7) is 100%.

I read elsewhere on this forum about creating another column with NOPRINT. Then, you can use that column for a where condition. The solutions indicate I'd have to change the format from a Print/Display Only format to something else. Yet, whenever I try to remove the PRINT and PRINTLISTONLY and save as a file like this HOLD AS (NAME) format focus, I receive an error message about no output.

So, clearly I'm missing something still.

Thoughts?

you don't need an extra column, just add a WHERE TOTAL:

 TABLE FILE HOLD
"Total/Row % Sales Across Region By Year"
"Hiding Midwest Region"
PRINT YEAR           AS 'Year'
      RPCTNortheast  AS 'Northeast'
	  RPCTSoutheast  AS 'Southeast'
	  RPCTWest       AS 'West'	                       
COMPUTE ROW_TOTAL/D12.2% = RPCTNortheast + RPCTSoutheast + RPCTWest; AS 'Row,Total'
WHERE TOTAL ROW_TOTAL NE 100
END 

Thank you for the reply.

Unfortunately, it's not quite what I'm looking for. Perhaps if I rephrase the question, it might help.

I only want to eliminate records when only the hidden column (in this case, it's known as RPCT7) results in 100 percent not the entire row total. The entire row total is always going to be 100 percent.

Would I just rephrase your solution

WHERE TOTAL ROW_TOTAL NE 100  

to be

 WHERE TOTAL RPCT7 NE 100  

quote:

...eliminate records when only the hidden column...results in 100 percent...

If the 'hidden column' (Midwest in this case) is 100 percent, wouldn't the sum of the other three columns be 0 percent?

So then, perhaps, add?:

WHERE TOTAL ROW_TOTAL NE 0;