How to use group by in Multi row formula

[Sunitha]

Hi All,

Please suggest how to use Group by option in multi row formula.

i would like to have 12 months sale based on summarised columns below.

attached files are for reference in 2018.3 version.

Thanks,

Sunitha 

[BradWerner]

Just a clarification question, are you looking to have one row per summarized columns (Period, Market Code, MPG, Business Unit) with the sales for only the last 12 months (2019 AP07 - 2020 AP06).

Or instead, are you looking for what the prior 12 months of sales are for any given Period for the summarized columns?

[DavidP · Accepted solution]

Hi @Sunitha 

In order for the multi-row field to work the way you want, you have to sort the data correctly first and decide how you want to group it.

For instance, I decided to group by Market Code, MPG and Business Unit. I also extracted Year and Month from Period and then sorted the data by Market Code, MPG, Business Unit, Year and Month.

Now you can add up 12 sequential rows while grouping by Market Code, MPG and Business Unit. You need to change the option Values for rows that don't exist to 0 rather than null. This way, if there are less than 12 months sequential data for a specific group, you still get an answer.

You can play around with the groups, just remember to change it in both the multi-row and sort tool configurations so that the correct values are added together.

[Sunitha]

Thank you so much i will try working on it.

Thank you for clear explanation.