To calculate the current day % increase in transaction volume vs. last 6 days in Alteryx

Hello everyone,

I am new to alteryx tool and trying to build a report which shows me the current day % increase in transaction volume compared to last 6 days.

Also, there is bifurcation by a 6 digit code and Merchant name. My data looks like the below:

Code | Merchant Name | 4-Jul | 5-Jul | 6-Jul |7-Jul |8-Jul |9-Jul |10-Jul |% Increase

123456 | ABCD | 0 | 0 | 0 | 40 | 80 | 100 | 200 | sum(4 to 9th Jul volume)/ 10 Jul volume

% Increase i.e.(0+0+0+40+80+100)/200

Appreciate any help and if possible a sample workflow would be great.

Reporting

Accepted answers

All comments

BrandonB

You can use a multi-row tool here in order to reference rows above or below the current row being calculated. You will want to increase the number of rows to 6 in the configuration window and then you can create your formula that sums the last 6 days and then divides it by the current.

Sree_analytics

Thanks Brandon for your response. Any possibility you can share a sample workflow as I am totally laymen to this tool.

Attaching some sample data.

sample data.xlsx

Elias_Nordlinder

Hello @Sree_analytics ,

(Edit, forgot -1 in the formula to see increase)

I can try to chime in on this, I have attached a workflow as well 🙂
This is how the workflow is running:

1. You have to use the Transpose tool first to get all the values in the same row, that is the starting point for using the Multi-Row formula in a later phase.

2. I used the Cleansing tool in between to change Null to 0 to be able to work with the multi-row formula tool.

3. When you have transposed the value you can now use the Multi-Row formula tool.

You have to configure it so that you can look "6 rows back", this is done in the top left of the tool where it says "Num Rows",

after this you write the formula you want to write, where "[Row-1]" means the row before the current row.

I used the formula:
"IF [Merchant Name] = [Row-6:Merchant Name]
THEN
([Value]/
([Row-1:Value]+[Row-2:Value]+[Row-3:Value]+
[Row-4:Value]+[Row-5:Value]+[Row-6:Value]))-1
ELSE NULL()
ENDIF"

for this.

(If the merchant 6 rows back is the same as the current merchant then divide the current value with the sum of the value for the six rows before this one.)

4. I then used a similar multi-row formula but for the increase compared to the 6 day average for 6 days back.

5. Finally I used a filter tool for if you only want to see the increase compared to six days back or see all the values (As there are only 7 days for the merchants now most of the (This day compared to 6 days before, will be Null)

If you want to configure so it runs if one of the 6 days before is not there, it needs a little change in the configuration.

Let me know what you think 🙂

vs 6 day back.yxmd

Emil_Kos

Hi @Sree_analytics,

I took a different approach and I didn't use the multirow formula. My solution is based on creating few additional variables using samples and summarizes tools and adding them together at the end.

It works and it is fully dynamic. It will always take into consideration the last 6 days of data.

The output:

Compare Previous day.yxmd

Sree_analytics

Thank you Elias & Emil. I will try both options and keep you posted.

Really appreciate the prompt response.

Sree_analytics

Thank you, Elias & Emil. I will try both options and keep you posted. Really appreciate the prompt response.

_____________

I just checked the workflow and there is a small change in the sample data. The raw data does not actually contain the below columns. I added them for reference. Apologies. I should have mentioned that.

So these 3 columns have to be generated in the workflow.

Grand Total

Sum last6days

% change with the current day volume

Attaching the sample data again without totals and percentages.

Also, another question I had while replicating the workflow. The dates in the excel sheet would keep changing every day so would that require any changes or the workflow will automatically consider the last 6 days. I see that currently the exact date columns have been selected.