2022.1.1.30569 Patch Release Update

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.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Dynamically Get last 30 , 60 and 90 days from month

adarsh2608
8 - Asteroid

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

 

GROUP2013201420152016201720182019202020212022Last 30 DaysLast 90 DaysLast120 Days
Direct FundSum 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 )
AssetSum 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 FundSum 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 SupportSum 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 )
3 REPLIES 3
binuacs
16 - Nebula

@adarsh2608 can you also provide the expected result in an excel file based on your input file?

adarsh2608
8 - Asteroid

Hello,
Below is the expected O/P

 

GROUP2013201420152016201720182019202020212022Last 30 DaysLast 90 DaysLast120 Days
Direct FundSum 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 )
AssetSum 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 FundSum 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 SupportSum 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 )

 

 

RolandSchubert
16 - Nebula
16 - Nebula

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. 

 

2022-05-09_14-15-46.png

 

Best,

 

Roland

Labels