Summarizing the data in months intervals

[GauravRawal]

Hi All,

I am having a data as attached below for each date in asset_changedate column. What i am trying to achieve is to group the data by asset number and asset status,by dividing the changedate in month intervals, starting from the present date, for any number of past years.

For ex, I need to find the total number of ACTIVE status rows for an asset in last month(starting from the present date when the flow runs), and then total number in previous month and so on.... And similarly for other statuses.

How could i achieve this?

asset_number	asset_status	asset_changedate
1280481008	ACTIVE	5/22/2016 0:00
1280481008	ACTIVE	5/24/2016 0:00
1280481008	ACTIVE	5/26/2016 0:00
1280481008	ACTIVE	5/28/2016 0:00
1280481008	ACTIVE	5/30/2016 0:00
1280481008	OOS	9/11/2016 0:00
1280481008	OOS	9/13/2016 0:00
1280481008	OOS	9/15/2016 0:00
1280481008	OOS	9/17/2016 0:00
1280481008	OOS	9/19/2016 0:00
80920	OOS	8/5/2016 0:00
80920	OOS	8/7/2016 0:00
80920	OOS	8/9/2016 0:00
80920	OOS	8/11/2016 0:00
80920	OOS	8/13/2016 0:00
80920	ACTIVE	8/15/2016 0:00
80920	ACTIVE	8/17/2016 0:00
80920	ACTIVE	8/19/2016 0:00
80920	ACTIVE	8/21/2016 0:00
80920	ACTIVE	8/23/2016 0:00

[NicoleJohnson · Accepted solution]

Here's an option:

1. I would start by bringing in the DateTimeNow input tool (In/Out category). This will update the date you're using to compare to today's date whenever the workflow is run. (Make sure you change the format to the standard yyyy-mm-dd format for ease of use later in the workflow.)
2. Append the date to your data (before or after filtering for Status = ACTIVE) using the Append Tool
3. Convert [asset_changeddate] to the proper date format (I did this in a formula, but you could also use the DateTime tool in the Parse category of tools)
4. Calculate the difference between today's date & asset_changeddate using this formula: DateTimeDiff([DateTimeNow],[asset_changedate],"months")
5. Use the Summarize Tool to Group By DateDifference (i.e. number of months in the past) and then Count the same field to see the total # of Active assets for each "Months Ago" group

In this example, "0" months will be assets that changed in the last month, then "1" will be from the month prior, etc. Month difference should be calculating based on the same day in the comparison month, so if you wanted to use a standard 28 days or something, you'd need to change from "months" to "days" and divide by your standard day count, etc.

Take a look at the attached workflow - I added a few more dates to check that it was working, seems to get the results you're looking for! Let us know if that works. Otherwise might help to see a sample of what you expect the output to look like? Thanks!

NJ

[GauravRawal]

Hello Nicole,

Thanks for your inputs. It got me the output i needed. 

Could there be a way to group the data per month wise also, for the same set of data?

For ex, if today is 24th May. I get the count of ACTIVE status in 24 days of May, April, March and so on, for past years data.

Thanks,

Gaurav

[GauravRawal]

Got this working. 

Thanks.