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

QUESTION: Adding Min and Max values to different IDs - Milestone Trend Analysis

Czaggy
7 - Meteor

Hello everyone,

 

I am trying to create a Milestone Trend Anlaysis (MTA) with Tableau and therefore need to transform my input data with Alteryx.

 

I have one project with different milestones at a different date. To create the orange line in the image below i need to create the MIN() and MAX() values from the dates and add them with the corresponding project ID to the table (MS00).

 

Czaggy_0-1578405707297.png

 

Project IDMilestone #ValueDate of Generation
548MS0115.05.202001.01.2020
548MS0223.05.202001.01.2020
548MS0315.07.202001.01.2020
548MS0413.09.202001.01.2020
548MS00MIN(Value)MIN(Value)
548MS00MAX(Value)MAX(Value)

 

To get the last two rows I was thinking of using the formula tool and then append the output to the rest of the table.

 

Now I dont really know how to do this if you have multiple project IDs and use this process for every individual project ID.

 

It should look like this - the min and max values are for the corresponding project ID and not for the entire column. Anyone with an idea?

Project IDMilestone #ValueDate of Generation
548MS0115.05.202001.01.2020
548MS0223.05.202001.01.2020
548MS0315.07.202001.01.2020
548MS0413.09.202001.01.2020
548MS00MIN(Value)MIN(Value)
548MS00MAX(Value)MAX(Value)
663MS0115.05.202001.01.2020
663MS0223.05.202001.01.2020
663MS0315.07.202001.01.2020
663MS0413.09.202001.01.2020
663MS00MIN(Value)MIN(Value)
663MS00MAX(Value)MAX(Value)

 

4 REPLIES 4
wdavis
Alteryx
Alteryx

Hi @Czaggy 

 

I have attached a workflow which should hopefully achieve this for you. The Summarize tool allows you to generate these Min and Max values for each Project ID. You then just need to go through a couple of steps to organise your data into the correct format, before using the Union tool to join it to your original dataset

 

Let me know if you have any questions!

 

Thanks

Will

Czaggy
7 - Meteor

Hi @wdavis,

thank you so much so far! Is there a way so that the Min/Max Values in the column "Date of Generation" actually display the min/max value of the column "Value" - I only want to calculate min/max for "Value" but putting it in both columns.

 

Ill try it out on my own too - you've done wonders so far!

 

Thank you very much.

 

 

EDIT: I solved it by leaving it empty and then using the formula IF [Date of Generation] = NULL Then [Value]

wdavis
Alteryx
Alteryx

Hi @Czaggy 

 

No problem, in this instance, all I have done is remove the value for Date of Generation from the Summarize tool. Then added in a new Formula tool, which creates a new field for 'Date of Generation' but is just copying the value for 'Value'

 

Any questions on this then let me know!

 

Thanks

Will

wdavis
Alteryx
Alteryx

I just saw your edit as well that you have solved it, nice work!

Labels