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.
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.
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 🙂
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:
Thank you Elias & Emil. I will try both options and keep you posted.
Really appreciate the prompt response.
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
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.
//Let me know if you have any questions
Elias
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.
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.