Combine Calculate With Pivot Table Syntax

2 messages
Open this post in threaded view
|

Combine Calculate With Pivot Table Syntax

 Greetings, Wondering if I can combine items labeled 2 and 3 below into one command? FYI, I would have uploaded visuals of the output, but didn't see an upload option. ************ 1) Create the table. CTABLES   /VLABELS VARIABLES=NPSCat DISPLAY=LABEL   /TABLE NPSCat [C][COUNT F40.0, COLPCT.COUNT PCT40.0]   /CATEGORIES VARIABLES=NPSCat ORDER=A KEY=VALUE EMPTY=INCLUDE   /CRITERIA CILEVEL=95. *********** 2) Add the NPS row by subtracting Detractor from Promotor. STATS TABLE CALC SUBTYPE="'Custom Table'" PROCESS=PRECEDING /TARGET FORMULA="x2-x0" DIMENSION=ROWS LEVEL = -1  LOCATION=2 REPEATLOC=NO LABEL="NPS" MODE=AFTER     HIDEINPUTS=NO /FORMAT CELLFORMAT="#,###.##" DECIMALS=0  INVALID="". ********** 3) Change the format to percent for the “Column N %” and use 1 decimal place. STATS TABLE CALC SUBTYPE="'Custom Table'" PROCESS=PRECEDING /TARGET FORMULA="x1" DIMENSION=COLUMNS LEVEL = -1  LOCATION='Column N %' REPEATLOC=NO LABEL="Column N %"     MODE=REPLACE HIDEINPUTS=NO /FORMAT CELLFORMAT="##.#%" DECIMALS=1  INVALID="". ***************************************************************************************************** **********Alternative method within Custom Tables. CTABLES   /VLABELS VARIABLES=NPSCat DISPLAY=LABEL   /PCOMPUTE &cat3 = EXPR([3] - [1])   /PPROPERTIES &cat3 LABEL = "NPS" FORMAT=COLPCT.COUNT PCT40.0 HIDESOURCECATS=NO   /TABLE NPSCat [C][COLPCT.COUNT PCT40.0]   /CATEGORIES VARIABLES=NPSCat [1, 2, 3, SUBTOTAL='SUBTOTAL', &cat3, OTHERNM] EMPTY=INCLUDE POSITION=AFTER. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Open this post in threaded view
|

Re: Combine Calculate With Pivot Table Syntax

 It's a bit hard to visualize what you are doing without seeing the tables, but if the CTABLE PCOMPUTE calculates what you want, you should use that.  PCOMPUTE is limited to calculations within the categories of a single variable and cannot deal with calculations that require different parts of the table, while TABLE CALC does not have those limitations.If your third syntax is just changing the format, you can do that within CTABLES in the statistics specification.IOn Wed, Aug 28, 2019 at 1:40 PM Chris Keran <[hidden email]> wrote:Greetings, Wondering if I can combine items labeled 2 and 3 below into one command? FYI, I would have uploaded visuals of the output, but didn't see an upload option. ************ 1) Create the table. CTABLES   /VLABELS VARIABLES=NPSCat DISPLAY=LABEL   /TABLE NPSCat [C][COUNT F40.0, COLPCT.COUNT PCT40.0]   /CATEGORIES VARIABLES=NPSCat ORDER=A KEY=VALUE EMPTY=INCLUDE   /CRITERIA CILEVEL=95. *********** 2) Add the NPS row by subtracting Detractor from Promotor. STATS TABLE CALC SUBTYPE="'Custom Table'" PROCESS=PRECEDING /TARGET FORMULA="x2-x0" DIMENSION=ROWS LEVEL = -1  LOCATION=2 REPEATLOC=NO LABEL="NPS" MODE=AFTER     HIDEINPUTS=NO /FORMAT CELLFORMAT="#,###.##" DECIMALS=0  INVALID="". ********** 3) Change the format to percent for the “Column N %” and use 1 decimal place. STATS TABLE CALC SUBTYPE="'Custom Table'" PROCESS=PRECEDING /TARGET FORMULA="x1" DIMENSION=COLUMNS LEVEL = -1  LOCATION='Column N %' REPEATLOC=NO LABEL="Column N %"     MODE=REPLACE HIDEINPUTS=NO /FORMAT CELLFORMAT="##.#%" DECIMALS=1  INVALID="". ***************************************************************************************************** **********Alternative method within Custom Tables. CTABLES   /VLABELS VARIABLES=NPSCat DISPLAY=LABEL   /PCOMPUTE &cat3 = EXPR([3] - [1])   /PPROPERTIES &cat3 LABEL = "NPS" FORMAT=COLPCT.COUNT PCT40.0 HIDESOURCECATS=NO   /TABLE NPSCat [C][COLPCT.COUNT PCT40.0]   /CATEGORIES VARIABLES=NPSCat [1, 2, 3, SUBTOTAL='SUBTOTAL', &cat3, OTHERNM] EMPTY=INCLUDE POSITION=AFTER. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD -- Jon K Peck[hidden email] ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD