how to Group by two columns and output min/max date using multiRow formula

[rajenlodh]

Hi there,

need some help on the tool Multi row formula.

i got data where need to group by Application reference,then milestone names and select min of started on in one columns and max of completed on in another columns with Multirow formula tool.

 i can do it by crosstab but want to use it via Multirow formula.anybody can help please.

simple task but not sure how to do without [summarize ->crosstab] use.

intend to use multirowformual.

if any other way to do happy to learn also.

[BrandonB]

Could you share what your desired output would be? It looks like in your data you have multiple rows for a given application reference, but your milestone names are unique for each application reference group. Which milestone name would you want to keep?

[BrandonB]

If you just need the min and max dates of the two columns and keep the application reference and milestone names as they are, the best way is probably to do a summarize where you group by the application reference, do a max of the column you need the max for a a min of the column you need a min for. Then join this data back to your original data on the application reference field and uncheck the columns of the old date fields just leaving the min and max columns to take their place.

[rajenlodh]

Want to keep all milestone names but min/ max of dates

[rajenlodh]

Hi mate, yes I know and use that but want to implement it via multi row
formula

[BrandonB]

You can technically do it with a multi row. First use a sort tool to sort by application reference and then by the first date you are interested. Make sure that you do ascending or descending based on which value you want on top. Then use a multi row tool and set non existent rows to null. For your formula say iif(isNull(row-1:date),date,row-1:date)

This means it will keep the top date and set every following row to that date. Check the box to group by application reference so it does this for each block of rows for each application reference.

Then sort by your next date field in the order you need and repeat this process with another multi row tool using the same formula as explained above.

[rajenlodh]

Thanks mate for the super explanation.is it possible to share with a
workflow please.
Will wait for your reply

[BrandonB]

Currently typing from my phone but I can send one tomorrow when I’m back in the office. Should be a fairly quick workflow to build unless someone else beats me to it!

[rajenlodh]

Thanks for your reply mate

[BrandonB]

Workflow example is attached. Basically just sorting the dates then multi row to set the earliest or latest date to each row.