Creating Pivots with CrossTab + Summary Tool

Question

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 · Answer

Hello @Maithre ,

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

Regards

count_nulls.yxmd

Maithre · Answer

Sure!

This is what my input data looks like

RecordIDReport DateReview Status113/09/2020Out of Scope213/09/2020Out of Scope313/09/2020Out of Scope405/10/2020Out of Scope505/10/2020Out of Scope612/10/2020Fail712/10/2020Out of Scope812/10/2020Out of Scope919/10/2020Pass1019/10/2020Pass1119/10/2020Out of Scope1219/02/2021Pending Review

This is my desired output:

Report DateCountWithNulls__Null_CountWithNulls_FailCountWithNulls_Out_of_ScopeCountWithNulls_PassCountWithNulls_Pending_ReviewTotal13/09/2020 16  705/10/2020 10112 2312/10/2020 18  919/10/2020 152 826/10/2020 251 8Total340131431218

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 · Answer

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