Multi Row Tool

[linhtran]

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 

[ShankerV]

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

[danilang]

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

[ShankerV]

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

[linhtran]

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.

[ShankerV]

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