hi- I ran into an issue that my Multi Row formula doesn't work correctly the way I want it if im missing weeks in between. As shown below, Its missing week 7 and 8 so my Multi row automatically picks up week 3 and 4 instead ( so 6 rows total) .My calculation is that calculate average of 5 weeks before and current to get the current week new Cost . What is the best way to fix this? How do i add missing weeks to my formula. Thanks a lot
Hi @linhtran .
The best way to overcome this issue is generate a row with 0 value where there are missing weeks.
Then send to the multi row formula, so you will get the expected result.
Many thanks
Shanker V
Hi @linhtran
Use a Generate Rows tool to generate all the weeks that you need from your min to your max. Join this list to your data, and union the J with the missing rows from the weeks list that you generated and sort by year and week. You'll need to replace all the null values with 0s
Dan
Hi @linhtran
Please find the sample use case for your scenario where you can apply the 5 month average logic at the end of Multi row formula tool.
Many thanks
Shanker V
I have this calculation by Location, Product, and Temp zone level. At the temp level (dry level), its missing week 7 and 8 but other temps have full 52 weeks in a year. Before the multi Row I sorted it by Location,Product,Temp zone, Year, Fiscal Week. Where do I add the Generate Row tool and how do I apply it to make the weeks that missing in a specfic Temp zone appear with 0 values. Appreciate your help.
Hi @linhtran
Please add the Generate rows tool before the multirow tool and after the sort tool in your workflow.
Also I have update the generate tool from 12 to 52.
Many thanks
Shanker V