Grouping based on dates

[ll98]

Hi All -- I'm looking to take from the data - the last date Batch GL Date  as well as its measures.  Presently, I'm able to capture it but it only captures 1 measure

For example -- The last GL Date 3/26/2000 but there are two values associated with the same date but its only grabbing one.

Fund	Batch GL Date	Contribution
Fund III	3/26/2000	1000
Fund III	3/26/2000	3000

Thanks 

[grazitti_sapna]

Hi @ll98,

I can see your workflow is fetching both the values for the date mentioned. Can you please elaborate on your problem and let me know your expected output?

[BrandonB]

The best way to do this is with a summarize tool to find the max batch GL for each fund and then join it back with a join tool. You will also need to convert the batch GL date to an actual date as seen below. Workflow is attached. 

 

[messi007]

@ll98,

Please see below:

Attached the workflow,

Regards,

[ll98]

  -- Thanks -- But I want to show is net of all the values associated with the  Max Date --   

Fund	Batch GL Date	Contributions
Fund III	3/26/2000	4000

[apathetichell]

I could be off-base here but I think what you are asking to do is to sum up the contribution information by date. Your formulas are all fine - but you should change your options on your Summarize.

If you swap your date from first to group by and then change your contribution to summarize you'll get the total entries per day. I believe that's what you are looking for.

Note: your date is a string presently - you'll want to use DateTime (set to mm/dd/yyyy) to convert it to time. I just uploaded a workflow which fixes the dates, sums and then sorts out the most recent date and selects that record.

[BrandonB]

@ll98 you can just add a summarize at the end of my workflow that does group by fund, group by batch GL date, and sum contributions. The primary focus of my workflow was to isolate the max date and appropriately pull in the corresponding lines. Once those have been identified, you just sum up the results.