Combine Calculate With Pivot Table Syntax

[ChrisKeran]

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 Peck]

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.

I

On 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

--
===================== 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