This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
The 2022.1.1.30569 Patch/Minor release has been removed from the Download Portal due to a missing signature in some of the included files. This causes the files to not be recognized as valid files provided by Alteryx and might trigger warning messages by some 3rd party programs.
If you installed the 2022.1.1.30569 release, we recommend that you reinstall the patch.
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
"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.