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.

[rajenlodh]

Hi Brandon, thanks for the reply and the flow,Appreciate so much your effort on it.

the flow looks promising pretty much, but before min max of dates, I need them group by Application_reference, then group by Milestone_names.

In the flow no sorting or used of Milestone names in the multiple row formula.

As i need to use it what should i do? 

[BrandonB · Accepted solution]

If you look at the configuration in the multi row tools I have it grouped by the application reference field. This means that the formula will only apply to each set of application reference numbers. The reason that I did not check the box for milestone name is because row 1 is "Type A Closed" and row 2 is "Type B Closed" for the same application reference. If I checked the box for milestone name it would not apply the actual started on date to each row in the application reference block, but rather only to rows with that specific Milestone Name within the Application reference. You can definitely use multiple group by fields, but in this case we just checked the box for the first one because we want to apply the min and max to every row within the same application reference value. 

[rajenlodh]

got it mate.:)

so as i need to group by Application_reference, then by Milestone name for min/max of dates what can I do? any solution please

[rajenlodh · Accepted solution]

tried to write  a code to find the minimum for each app_ref_then group by milestone name.

attached screesnshot & code here

if(([Application_Reference]=[Row+1:Application_Reference])
and ([Milestone_Name]=[Row+1:Milestone_Name]))
then

if(([Application_Reference]=[Row-1:Application_Reference])
and ([Milestone_Name]=[Row-1:Milestone_Name])) then
min([Actual_Started_On],[Row+1:Actual_Started_On],[Row-1:Actual_Started_On],[Row-1:min_started_On])
else
min([Actual_Started_On],[Row+1:Actual_Started_On])
endif

elseif(([Application_Reference]=[Row-1:Application_Reference])
and ([Milestone_Name]=[Row-1:Milestone_Name]))
then
min([Actual_Started_On],[Row-1:min_started_On])

else
[Actual_Started_On]
endif

plus from the flow you shared, cna do with soritng by app_ref, then milestone_name, then sort by startedon date ascending.

then on the running total instead of only  App_ref, used both app ref,milesonename.