Convert to crosstab from raw data

Hi Guys,

I have a sample of dataset that i want to create a corsstab to count the number of customer with specific condition like:
- No.of.Customer buy something on 2018-06-02 and have stage=1 & Status=Y

- No.of.Customer buy something on 2018-06-02 and have stage=1 & Status=N

- No.of.Customer buy something on 2018-06-02 and have stage=2 & Status=Y

- No.of.Customer buy something on 2018-06-02 and have stage=2 & Status=N

...

- Total of Quantity by date.

please prefer to the image below for detail, i tried to use crosstab & transpose from Transform tab but it seems like to unable to do this. Please help to advice me the solution!

Thank you so much!.
Regards,
M.

table1 is source, table 2 is target

Transformation

Expression

Accepted answers

Treyson

The sum tool is pretty rad and I want you to know how to use it in the future so let's learn how to fish!

Below is a picture of the configurations panel for the Sum tool, if you look at the Fields area, there is a list of all fields that are coming into the tool. The "Actions" area is where the magic happens in the tool. if you select a field from the list above (clicking on the field highlights and "selects" it), there is a drop down option in the actions pane that allows you to select what functionality the sum tool is performing. Since we have already decided to group on "OrderDate", "Stage" and "Status" the sum tool is going to bring us to whatever aggregation we are asking to to do on those distinct values.

Since you said you wanted to do a count of the customer IDs, selecting that field and then through the dropdown selecting count (or Count Distinct) will adjust what we are aggregating on. If you need to remove a field that already exists within the tool, there is a button on the side of the actions pane that is a circle with a "-" within. Just click that while selecting whatever field you want to remove. Since the name of the aggregated field is changing, this will throw an error message on the "cross tab" tool. You will have to adjust that to sum on our new "count_CustID" field.

Let me know if this needs any further clarification.

SUm Tool.png

All comments

BenMoss

The detail from between the first statement and the ...'s which I assume means all possible cases can be generated using a single summerize tool.

GROUPBY 'Date', 'Stage' and 'Status', and SUM your 'Quantity'

You can then cross-tab this into the format you like and one additional option then is to create a total column.

If you attach the sample data shown in your post then I can build out an example for you.

Ben

Treyson

Hello @MichaelJack

You will first need to summarize before the Cross Tab. Please see the attached.

Treyson

MichaelJack.yxmd

MichaelJack

@Treyson:

Thank for your response, the formula should need to be fixed a bit, the no.Customer(Stage=1 && Status=Y) means we need to count CustomerID follow the condition. But the Quantiy = SUM(Quantity) over all of them.
It appreciated if you can help to modify the file and i think the result will be perfect!

Thank in advanced.
Regards,
M.

AndyMoncla

This may not be exactly what you want but it might help.

Cheers!

CrossTab and Transpose.yxmd

AndyMoncla

By the way, I misread your request. I thought you were looking for the customer ID under each category. Sorry.

CrossTab and Transpose with count.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335