I'm trying to get a bar chart that would summarize the total amount of turnover by fiscal year and by Business Unit.
A 1st dropdown will allow you to choose the month. A 2nd dropdown will select the year. Thus the selected month will determine the calculation period of the cumulative turnover, from the beginning of the fiscal year to the month selected in the dropdown.
The fiscal year starts every 1st of October. For example:
- If I select in a dropdown the month of January, then in a second dropdown the year 2020, we would want the total from 10/01/2019 (mm/dd/yyyyy) to 01/31/2020.
- If I select December 2019, I would like the cumulation from 10/01/2019 (mm/dd/yyyy) to 12/31/2019. SAP analytics cloud gives a limited choice of cumulations.
On the "turnover" measure, there is an "Add time Calculation" functionality that gives calculations on periods such as "previous", "over", "to date".
However, even if you make for example a "Month/fiscal period over period", it does not give the cumulation of the fiscal period as you can see. If we look at the SO BU, we don't see a cumulation between February 2019 to September 2019, since from March 2019 to April 2019 we can also analyze a decrease in turnover, except that should return a cumulation.
Same if we choose "Month/fiscal period to date".
Also, we are obliged to have at least one drill down at the month level: we cannot get an aggregated view per year and per BU, in which case we get an error message.
Have you had this same problem? If so, did you manage to solve it?
You need to create date-based aggregation, story based (one-time) or model one, that can be reused later.
Select Create calculation in measures tab
Then select SUM operation type in operation field
(source: sapanalytics.cloud)
Then tick Use conditional aggregation checkbox and add necessary conditions. In your case it would be Select by Range where you can define date range, in your case from 10/01/2019 to 12/31/2019.
More about defining ranges and calculations here.