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!

Dallas-Ft. Worth, TX

Welcome to the Dallas-Ft. Worth User Group

Click in the JOIN GROUP button to follow our news and attend our events!

Calculating % Difference From Previous Week

jkv0019
8 - Asteroid

Hello Everyone,

 

I am new to Alteryx and was wondering if I could ask for help with a workflow I have. 

 

I pull from SQL Server to get the orders we have processed from each supplier. I have put together a workflow that gives me a base for what I would like to accomplish. However, I'm stumped by how to add a column in that calculates the difference in orders between Column A and Column B, B being the following week and will be dynamic as more weeks are added.

 

Also, I am having a little trouble with returning the correct week number, as you can see below (2021-00). I used DateTimeFormat([Act Arr CST],"%Y") +'-'+ DateTimeFormat([Act Arr CST],"%W").

 

If anyone has any suggestions, can you please provide them?

jkv0019_0-1614176891973.png

 

jkv0019_2-1614176919912.png

 

2 REPLIES 2
Joe_McCoy
8 - Asteroid

I believe you can find the solution to your week number in the following post or one of the links there:

Solved: Calculating the week number - Alteryx Community

 

As for your rolling calculation, do you have a sample of an output that you would like the data to look like?

 

Thanks,

Joe

BPurcell2
9 - Comet

Hi @jkv0019 ,

 

Welcome to Alteryx.  Usually, there are multiple ways to solve a problem with the tool.  Here's my suggestion:

 

1.  Transpose your data, basically you want your dates to run vertically in a column as opposed to rows.

2.  If the date is not already in a date value, you'll want to change it -- Alteryx reads dates as YYYY-MM-DD.  If your query doesn't already aggregate the data at a week view, you can do so by using the link provided by @Joe_McCoy 

3.  Sort your data, first group by any necessary dimensions -- product group, location, etc.  then sort by week date

4.  Use the multi-row formula to perform a calculation based off the row above [Active Row: Value] - [Row-1:Value] -- or slightly different if you are looking for % change

5.  If desired, add a formula to describe your week date value -- 2021: Week 2 -- parsing the date value or using a datetime formula

 

One other note, this section of the forum may not get as many views as a more general section -- https://community.alteryx.com/t5/Alteryx-Designer-Discussions/bd-p/designer-discussions  -- when I have a technical or help question, I post it here -- there are alot of helpful Alteryx users across the world.

 

Good luck