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.

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

Sree_analytics
6 - Meteoroid

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.

9 REPLIES 9
BrandonB
Alteryx
Alteryx

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
6 - Meteoroid

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.

Elias_Nordlinder
11 - Bolide

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 🙂

 

Elias_Nordlinder_0-1626953619797.png

 

Elias_Nordlinder_0-1626954472692.png

 

Elias_Nordlinder_1-1626954515575.png

 

 

Emil_Kos
17 - Castor
17 - Castor

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.

 

Emil_Kos_0-1626954099187.png

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

 

The output:

 

Emil_Kos_1-1626954214567.png

 

Sree_analytics
6 - Meteoroid

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

 

Really appreciate the prompt response.

 

Sree_analytics
6 - Meteoroid

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.

 

Thanks

Elias_Nordlinder
11 - Bolide

Hello again @Sree_analytics ,


I did not use the extra columns earlier as I understood it should be calculated without them.

Regarding changing of names it should be dynamic now.

I added so the input for the transpose tool will take in dynamic values.

 

If you change so the column furthest to the left (15/07/2021) is removed and a

new column (22/07/2021) is added furthest to the left the workflow will work with this

change in configuration as well.

 

The only important thing for it now is that columns is in the same order as now 
(First column in C and sixth column in I).


But you can try it out with different days and see how it works 🙂

 

(Snippet when changing dates)

 

PS. I also added a "6 days sum vs last day calculation" as you specified it like that in the original question.

But it is just to the division in reverse.

 

Elias_Nordlinder_0-1626956663708.png

 


//Let me know if you have any questions
Elias

Emil_Kos
17 - Castor
17 - Castor

Hi @Sree_analytics,

 

Can you please check once more? 

 

I think the only problem would be if you would have a different file structure but I hope this will not happen in your data sets.

Sree_analytics
6 - Meteoroid

Hello Again, 

 

I will give this a shot with real-time data and let you know how it goes.

 

Thanks a ton for your help.

Labels