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

[Czaggy]

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).

Project ID	Milestone #	Value	Date of Generation
548	MS01	15.05.2020	01.01.2020
548	MS02	23.05.2020	01.01.2020
548	MS03	15.07.2020	01.01.2020
548	MS04	13.09.2020	01.01.2020
548	MS00	MIN(Value)	MIN(Value)
548	MS00	MAX(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 ID	Milestone #	Value	Date of Generation
548	MS01	15.05.2020	01.01.2020
548	MS02	23.05.2020	01.01.2020
548	MS03	15.07.2020	01.01.2020
548	MS04	13.09.2020	01.01.2020
548	MS00	MIN(Value)	MIN(Value)
548	MS00	MAX(Value)	MAX(Value)
663	MS01	15.05.2020	01.01.2020
663	MS02	23.05.2020	01.01.2020
663	MS03	15.07.2020	01.01.2020
663	MS04	13.09.2020	01.01.2020
663	MS00	MIN(Value)	MIN(Value)
663	MS00	MAX(Value)	MAX(Value)

[wdavis · Accepted solution]

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]

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]

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]

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