Creating Pivots with CrossTab + Summary Tool

[Maithre]

Hi,

My Input to the cross tab changes every week, and hence the column headers can also change week on week depending on the incoming data. The problem is I am trying to create a total summary after the cross tab. Since the column headers keep changing, how to automatically update the columns to summarize in the summary tool without any manual intervention.

Thanks!

[afv2688]

Hello @Maithre ,

The cross tab tool offers yo a variety of calculations which are already on the summarize tool (Sum, average, concatenate, etc.)

What are you trying to do? This would be useful to help you.

Regards

[Maithre]

Sure!

This is what my input data looks like

RecordID	Report Date	Review Status
1	13/09/2020	Out of Scope
2	13/09/2020	Out of Scope
3	13/09/2020	Out of Scope
4	05/10/2020	Out of Scope
5	05/10/2020	Out of Scope
6	12/10/2020	Fail
7	12/10/2020	Out of Scope
8	12/10/2020	Out of Scope
9	19/10/2020	Pass
10	19/10/2020	Pass
11	19/10/2020	Out of Scope
12	19/02/2021	Pending Review

This is my desired output:

Report Date	CountWithNulls__Null_	CountWithNulls_Fail	CountWithNulls_Out_of_Scope	CountWithNulls_Pass	CountWithNulls_Pending_Review	Total
13/09/2020		1	6			7
05/10/2020		10	11	2		23
12/10/2020		1	8			9
19/10/2020		1	5	2		8
26/10/2020		2	5	1		8
Total	3	40	131	43	1	218

And I have used a cross tab + Summarize tool to do this.

In the Cross Tab , I have used the Review Status as column headers. And then in the Summary tool, I calculate sum of these columns and create a Totals Row. then union both my summarize and cross tab outputs.

The problem is the Review status data pretty much keeps changing.. Since I am fixing the columns (for the ones whose sum is to be calculated) in the summary tool, I run into errors when the previously existing column header is not available

Any other workaround to create such a pivot would be much appreciated.

[afv2688]

Hello @Maithre ,

How about doing it the other way? First the summarize and then the crosstab like in this example?

Regards