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

% Change calculation at row level

Usamah22
8 - Asteroid

Hello,

 

I am trying to do some calculations on data that looks like below. I want to take the average of the latest 2 days of sales and compare it to the average of the previous 5 days. Trying to use a multi row formula. Can you help please?

 

DateSales
28/02/20209
29/02/20208
01/03/202010
02/03/20208
03/03/20209
04/03/202015
05/03/202022
06/03/202020
7 REPLIES 7
RolandSchubert
16 - Nebula
16 - Nebula

Hi @Usamah22 ,

 

you can your the Multi-Row Formula tool for that. Attached a sample workflow, hope this is helpful.

 

Best,

 

Roland

Assaf_m
11 - Bolide

Another approach using the sample tool.

Usamah22
8 - Asteroid

Thanks, this is getting me somewhere. Just a bit more detail i should have mentioned before. I have different products in the data as below.

 

ProductDateSales
a28/02/20209
a29/02/20208
a01/03/202010
a02/03/20208
a03/03/20209
a04/03/202015
a05/03/202022
a06/03/202020
b03/03/202055
b04/03/202052
b05/03/2020100
b06/03/2020121

 

I need to get the latest % change and then do a cross tab which will make the latest % change a column as below. How do i only take the latest % change from the multirow?

 

Product28/02/202029/02/202001/03/202002/03/202003/03/202004/03/202005/03/202006/03/2020% Change
a981089152220latest 5 days vs avg of previous
b    5552100121 
RolandSchubert
16 - Nebula
16 - Nebula

Had to changed the formula to limit calculation to the last available date. Then I added a Cross-Tab tool to switch rows to columns and a Join tool to add the "% Change", Dynamic Rename is to adjust the column headers. What do you think?

Usamah22
8 - Asteroid

So close but need to tweak it. There are some products that have no sales for the most recent days. For example below there are no sales for 5 and 6 March so the multirow will take average of 3 and 4 march. I need it to take the average of 5 and 6 march even if there are no sales ie. be null.

 

ProductDateSales
a28/02/20209
a29/02/20208
a01/03/202010
a02/03/20208
a03/03/20209
a04/03/202015
OllieClarke
15 - Aurora
15 - Aurora

@Usamah22 how about this approach? (I gave two ways of displaying the null values)

 

edit: included the cross-tab

OllieClarke_0-1583849215173.png

 

 

RolandSchubert
16 - Nebula
16 - Nebula

Now this also should be covered (hopefully) ... let me know, if it works.

Labels