Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Dynamic Year / Quarter / Month / Week Reporting

Uli123
7 - Meteor

Hello everyone,

 

I am currently trying to automate a weekly report that tracks purchases made based on product. the report is summarized by quarter and year when each quarter ends. Then we summarize by single month once the previous month is completed. Lastly, we summarize by weekly intervals until it completes and then can be consolidated into a monthly column. I have a workflow that worked fine through February, however, now that was in March, the process breaks. I am unable to find a solution that is dynamic enough to roll up the monthly columns once the month changes.

 

below is an example of how the weekly reporting should appear. obviously now that we have completed February, the workflow would have to roll up February similar to January, and then show the first week ending in March.

 

I would love to see how others solved dynamic reporting such as this in Alteryx.

 

ProductQ2 2018Q3 2018Q4 2018Q1 2019Q2 2019Q3 2019Q4 2019January 20192/3/20202/10/20202/17/20202/24/2020Total purchases
shirt0000111141820000145
shoes401342213706093726822778868826611918839901
hat32179674967300101100
pants0000398237500000496
socks00095000000014
Grand Total721343213867574735424378918826612018841656
5 REPLIES 5
Philip
12 - Quasar

Here's how I did it, if I understood what you need correctly.

 

First, check to see if the reporting date month is the same as the month today. If so, report by week.

 

Second, check to see if the reporting date quarter is the same as the quarter today. If so, report by month.

 

Else, report by quarter.

 

I did change the formatting so that the Cross Tab tool ordered the columns correctly. They could be renamed however you'd want using the Dynamic Rename tool.

 

 

DynamicTimeReporting.png

 

DynamicTimeReporting-output.png

danilang
19 - Altair
19 - Altair

Hi @Uli123 

 

Here's a different technique from @Philip's to get columns in the proper order and dynamically generate the future week column names when you don't yet have sales data for them.

 

w.png

The tools in the Generate dates... container generate dates until the end of the current month and assign column names to them based on their week.  These dates are joined with the input data to capture any sales for the period and all of the these are unioned with the previous months' data.  After filling in product names that missing, calculate the output column names for the previous data either rolled-up to previous month or the previous quarters.  The bottom container gets around the fact that the Cross tab will often, though not always, reorder the output columns alphabetically.  You get around this by adding a numeric ColumnID as using this as the column names in the cross tab.  After this there are 2 dynamic renames.  The first removes the "Sum_" that's added as a prefix to the transposed column names when you include a total row.  The second renames the columns the correct names.  The final Multi-Field tool replaces the nulls that were output by the Cross Tab tool for future weeks data with 0.  The result looks like this

 

r.png

 

Note the last weekly column to capture sales from the last Saturday to the end of the month, once they occur.

 

Dan

 

Uli123
7 - Meteor

Hello @Phillip, thank you for putting this solution together it in fact does the roll-up i was looking for exactly. and i tweaked it to work with my overall workflow and i like how the formula is able to bucketize all my sales by product within a column that i later transpose. 

 

I am running into an issue however, which is that it isnt quite dynamic enough. for example, as you can see below, i am creating a grand total row after the dynamic rename ( to remove the pre-fix "Count" ) and im running into the issue that there isnt a way to select "Unknown" fields in the Summarize tool, and therefore when new weeks or months populate it will not be included, unless i manually go back in to check the box for the new week / month. Have you encountered a workaround to ensure your unknown fields which populate in the future are included in the summarize tool?

 

Uli123_0-1583767093727.png

Uli123_1-1583767105169.png

 

 

Uli123
7 - Meteor

@danilang I really like your approach here, and it seems very dynamic month over month. Additionally, its usefull in the cross tab to include a total column and row which i was unaware could be done in that tool, and was adding that in later in my original workflow. 

 

I have two questions, shouldnt January be displaying as a month rollup not as a Q1? and secondly, is there a way that you know of, which allows us to only display up to the current week? i dont think we would need to see all of the future weeks in the month as they will always be null / 0 data. 

 

Uli123_0-1583769697284.png

 

danilang
19 - Altair
19 - Altair

Hi @Uli123 

 

A few small changes to only generate days until the next Saturday and expand all previous months in the current quarter

 

R2.png

 

Dan

Labels