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.
Solved! Go to Solution.
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
Hello @MichaelJack
You will first need to summarize before the Cross Tab. Please see the attached.
Treyson
@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.
Hi Andy,
thank for you help! but still loss the SUM of quantity.
Regards,
M.
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. :)
Thanks Treyson,
I am practicing on that and let you know when i completed, it will take time because im new in Alteryx haha.
thank for your help.
regards,
M.