Name a row

Hello community,

I have a dataset from which I am creating a series of summaries - total transactions and investment per quarter, grouped in one output by seller location, and in another by buyer location.

In my incoming data, these are sometimes blank.

I have processed the data, and am using a CROSSTAB tool to pivot the data, with "country" in rows and quarter (2018.3, 2018.4 etc) as the column headers. NULL appears as one of the countries.

I have used a SUMMARIZE tool to create a grand total for each column, and I have added this (using UNION) to add the grand total to the bottom of the CROSSTAB output.

However, (1) as the SUMMARIZE output does not have a column header, it does not have a row name when I join the two sets of data, and (2) the NULL country rows are listed as "NULL".

Is there a way I can label a row? I realize in writing this that there was (very probably) a less clunky way of adding a grand total.

An example of the output is below:

Thanks

DoC

Developer

Common Use Cases

Accepted answers

JosephSerpis

Hi @DavidOliverChapman its worth noting that the crosstab tool has Total column functionality in the "Method for aggregating values" so you may not need a summazise at all if you use that functionality.

All comments

JosephSerpis

Hi @DavidOliverChapman if you add in a formula tool and add this syntax IF IsEmpty([C&WBuyerRegion2]) THEN "Total" ELSE [C&WBuyerRegion2] ENDIF it should replace the null with Total you want to update the existing column.

brgilbert

Hi @DavidOliverChapman ,

To answer your question simply, yes you can label a row. There are going to be multiple ways to accomplish that task you're trying to accomplish.

In my opinion, the simplest solution to your current workflow is to use the Formula Tool to "label the row" using and IF/THEN.

IF IsNull([FIELD]) THEN "Total"

ELSE [FIELD]

ENDIF

That's the brute force method to label your total row.

One of many other solutions is to utilize the CREW macro: Add Totals. This macro will save you the summarize/union steps and solve "Total" label issue. The Add Totals tool gives you the option of adding row totals, column totals, or both.

http://www.chaosreignswithin.com

Hope this helps!

JosephSerpis

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335