Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Format Data

Chirag_Gandhi07
8 - Asteroid

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!                                                              

9 REPLIES 9
Thableaus
17 - Castor
17 - Castor

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,

papalow
8 - 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.

Chirag_Gandhi07
8 - Asteroid

@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?

Thableaus
17 - Castor
17 - Castor

@Chirag_Gandhi07 

 

Try this one attached. Version 2018.3

 

Cheers,

Chirag_Gandhi07
8 - Asteroid

@Thableaus Didn't seem to work

Thableaus
17 - Castor
17 - Castor

@Chirag_Gandhi07 

 

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

 

Cheers,

Chirag_Gandhi07
8 - Asteroid

@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!

Thableaus
17 - Castor
17 - Castor

@Chirag_Gandhi07 

 

Summarize and Formula Tool:

 

formulatool1.PNGsummarize11.PNG

 

 

Cheers,

Chirag_Gandhi07
8 - Asteroid

@Thableaus Thank you!

Labels