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?
Date | Sales |
28/02/2020 | 9 |
29/02/2020 | 8 |
01/03/2020 | 10 |
02/03/2020 | 8 |
03/03/2020 | 9 |
04/03/2020 | 15 |
05/03/2020 | 22 |
06/03/2020 | 20 |
Solved! Go to Solution.
Hi @Usamah22 ,
you can your the Multi-Row Formula tool for that. Attached a sample workflow, hope this is helpful.
Best,
Roland
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.
Product | Date | Sales |
a | 28/02/2020 | 9 |
a | 29/02/2020 | 8 |
a | 01/03/2020 | 10 |
a | 02/03/2020 | 8 |
a | 03/03/2020 | 9 |
a | 04/03/2020 | 15 |
a | 05/03/2020 | 22 |
a | 06/03/2020 | 20 |
b | 03/03/2020 | 55 |
b | 04/03/2020 | 52 |
b | 05/03/2020 | 100 |
b | 06/03/2020 | 121 |
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?
Product | 28/02/2020 | 29/02/2020 | 01/03/2020 | 02/03/2020 | 03/03/2020 | 04/03/2020 | 05/03/2020 | 06/03/2020 | % Change |
a | 9 | 8 | 10 | 8 | 9 | 15 | 22 | 20 | latest 5 days vs avg of previous |
b | 55 | 52 | 100 | 121 |
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.
Product | Date | Sales |
a | 28/02/2020 | 9 |
a | 29/02/2020 | 8 |
a | 01/03/2020 | 10 |
a | 02/03/2020 | 8 |
a | 03/03/2020 | 9 |
a | 04/03/2020 | 15 |
@Usamah22 how about this approach? (I gave two ways of displaying the null values)
edit: included the cross-tab