Format Data

[Chirag_Gandhi07]

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!                                                              

[Thableaus · Accepted solution]

Hi @Chirag_Gandhi07 

Here's a solution:

- 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]

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

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

@Chirag_Gandhi07 

Try this one attached. Version 2018.3

Cheers,

[Chirag_Gandhi07]

@Thableaus Didn't seem to work

[Thableaus]

@Chirag_Gandhi07 

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

Cheers,

[Chirag_Gandhi07]

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

@Chirag_Gandhi07 

Summarize and Formula Tool:

Cheers,

[Chirag_Gandhi07]

@Thableaus Thank you!