1 Reply Latest reply on Jul 19, 2018 5:28 PM by Ken Flerlage

# Split and calculate year and monthly information

Hello All,

I have need to create a report similar to the below example. Can you please explain how this can be achieved?

Calculation: Sample store data as reference however added an year to the order date..

Quantity = Number of Items Sold

Sales = Total Sale Amount

Discount = % Discount

Actual Discount is derived by percentage and Sales amount

 FY 2017 Jan 2018 Feb 2018 Mar 2018 Apr 2018 FY 2018 Quantity 820 597 363 885 733 645 Sales 50,767 43,971 20,301 58,872 36,522 39,917 Discount 16% 16% 18% 13% 19% 17% Actual Discount 8,123 7,092 3,694 7,703 7,101 6,671

For the above table. All the FY columns need to have the total for the year divided by number of months (Average). where as for the current year, I need to split and show the actuals.

I can separately calculate the columns and add them to the view however the challenge is that every time the month changes -- next month needs to be added automatically for the current year data.

Example - when I move to June date, May column should be added automatically next to Apr.