Hello I need to summarize the excel (attached) here it should be group by year, and group
expected o/p as below. it should sum the values and update it in the each column respectively for year and group . values and year are dynamic which may change every year . last 30 ,60,90 and 120 days should show the month based. as in the year column there is only month and year .
so in last 30 days it should show data of entire previous month ,even if current day is 1st of May it should show all the data of April
and similarly if it is last 60 days it should show last 2 months data and 90 days is last 3 months data
GROUP | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | Last 30 Days | Last 90 Days | Last120 Days |
Direct Fund | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) |
Asset | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) |
Mutual Fund | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of value |
Tech Support | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) |
Solved! Go to Solution.
@adarsh2608 can you also provide the expected result in an excel file based on your input file?
Hello,
Below is the expected O/P
GROUP | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | Last 30 Days | Last 90 Days | Last120 Days |
Direct Fund | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) |
Asset | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) |
Mutual Fund | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of value |
Tech Support | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value ) | Sum of (value )
|
Hi @adarsh2608 ,
"last 30 days" - you can usually solve problems like this using DateTime functions.
But - let's split the problem in two parts. First part is the calculation of "year" columns (2013, 2014, 2015,...). To calculate these columns, you have to take the "year-part" from the [year] column. A Formula tool with a LEFT function may be helpful to do so. Then a Summarize tool can be used to calculate the total by group.
For the second part (calculation of last 30/60/90 days) I would use a Text Input tool to create a list of the columns, a sample entry for start and end of the period ranges and the number of months to include (in addition to the last one).
DateTimeFunctions can be used to calculate the start and end for the 3 ranges - DateTimeFirstOfMonth e.g. returns the first day of the current month, using DateTimeAdd you can go one day back to get the last day of the previous month - that's the end of all 3 period ranges. The same logic can be used to calculate the start - it's one/two/three months back.
Using Append Fields you can add all these ranges to your input data, select only the rows within the period ranges and aggregate by range and group. For the comparison, the [Year] column has to be converted to a date using DateTimeParse.
Union both results and transpose are the final steps.
I think, it should work that way. Give it a try and let me know, if it works for you or if there are any additional questions.
Best,
Roland