community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Format Data

Hi everyone,

 

I have a workflow with dates and I need to break out the data by the number of actions completed per month. Below is an example of how my data currently looks, followed by another table of how I want my data to look.

 

Item                         Sub-Item                       Group                        Member                   ID                   Level                               Date

Summit               Attendee Request         Summit Support            Jai Patel                abc123           First Level              2018-07-28 04:37:37 

Summit               Attendee Request         Summit Support            Jai Patel                abc123           First level               2018-07-29 02:21:34

Summit               Attendee Request         Summit Support            Jai Patel                abc123           First level               2018-08-01 04:47:51

Alteryx                Annual License              Analytics Support         Mark James           def456           Second level          2018-09-07 06:34:18

 

How I would like the data to look:

 

Item                         Sub-Item                       Group                        Member                   ID                   Level                   18-July               18-August            18-September

Summit               Attendee Request         Summit Support            Jai Patel                abc123           First Level                 2                             1

Alteryx                Annual License              Analytics Support         Mark James           def456           Second level                                                                           1

 

So the second table displays the same fields but displays only one record if Item, Sub-Item, Group, Member, ID, and level are the same and breaks out the date into months and displays the number completed in those months. So Jai completed two in July for Attendee Request so a 2 is displayed under the 18-July field.

 

Any help would be greatly appreciated. Thanks!                                                              

Alteryx Certified Partner
Alteryx Certified Partner

Hi @Chirag_Gandhi07 

 

Here's a solution:

 

workflowexample1213.PNG

 

- Add a field with Year-Month date

- Summarize tool grouping by everything except Date field and count the new field

- Use Cross-tab Tool to put the Year-Month in column form.

 

Workflow attached (version 2018.4)

 

Cheers,

Asteroid

@Chirag_Gandhi07 

I would approach this problem using the following steps.

1.  Convert the date field to a year and month format.

2.  Crosstab your data with Item, Sub-item, Group, Member, ID, and Level appearing as columns and counting the number of dates.

Highlighted

@Thableaus Thanks for your help. I tried opening your workflow but it didn't work. I have alteryx designer 2018.3 x64. Do you know how I could get it to work?

Alteryx Certified Partner
Alteryx Certified Partner

@Chirag_Gandhi07 

 

Try this one attached. Version 2018.3

 

Cheers,

@Thableaus Didn't seem to work

Alteryx Certified Partner
Alteryx Certified Partner

@Chirag_Gandhi07 

 

2018.1 now. If it doesn't work, could you show what error message is displayed?

 

Cheers,

@Thableaus It asks me for a new license number. But, could you share screenshots from your summarize tool because I think that is where I am doing something wrong. Thanks!

Alteryx Certified Partner
Alteryx Certified Partner

@Chirag_Gandhi07 

 

Summarize and Formula Tool:

 

formulatool1.PNGsummarize11.PNG

 

 

Cheers,

@Thableaus Thank you!

Labels