How to Create a Column Set to have Excel Formula in between each Column of 5 Years?

You can create a Column Set that includes Excel formulas between each of five years to perform custom calculations or analyses. Here's how to set this up:

1. Navigate to Reports and select Add > Report Set.

2. Select Static as the type.

3. Enter a unique code and name.

4. Choose the appropriate Reporting Area (e.g., Financial) and select Measures for the Dimension.

5. Click Save.

6. Insert lines corresponding to each of the five years you want to include.

7. For each line, click Add to add the Rule and define the rule to reference the specific year.

   • For example, to reference the year 2021, you might set the rule to [Actual].[2021].

8. After each year's column, insert a new line to serve as the formula column. Set the Type of these lines to Excel.

9. In the Formula/Rule field, enter your desired Excel formula.

   • For instance, to calculate the difference between 2021 and 2020, you might use =C{row}-B{row}, assuming columns B and C correspond to 2020 and 2021, respectively.

10. Set appropriate formatting for each column, such as number formats, percentages, or currency.

11. Define any necessary properties, like hiding certain columns or setting specific display options.

12. Click Save, after configuring all lines and formulas.

13. Apply this Column Set to your Dynamic Report by selecting it for the column axis.