We have a scenario where we want to show the list of KPI values on the different levels – yearly and quaterly. Yearly value for all of them (except one) is the sum of monthly values. Exception is a KPI where value for a year is not the sum of the values but the value of the last month only in that year.
For example, if we want a yearly report for 2020 for that KPI, the correct yearly value would be the value of the in the December 2020.
The questions here are:
I.e When selected quaterly – should show the data from the last month of the quater. Months – 3,6,9,12 when it is a previous year, or in case of the current year – only the last month of the last previous quater.
Thank you in advance,
Also how can we use dates in formulas, e.g. do something when month is 3,6,9,12
Thank you for writing to us.
Our team has investigated all your questions and came up with several ideas.
However, to suggest an appropriate solution, we need to understand how your data is organized (years, months, etc.).
Could you please send us your data structure or just some sample data?
This will greatly help us to understand your case better.
Looking forward to hearing from you.
Thank you for the answer.
We are providing you a JSFiddle with our data sample (just a subset of the data).
Hope that this will help you to understand our problem better.
Thank you for preparing the example.
Please find our suggestions below.
filterobject should be specified for the field in the
slice. Here is a code snippet for your reference:
We have also modified your example by adding mentioned filter: http://jsfiddle.net/flexmonster/7udwe6ka/.
The filter can also be configured via UI: click on the field name on the grid and select a desirable month.
"Last..."filter can be used: http://jsfiddle.net/flexmonster/m8s2whcg/.
"Filter by dates"->
year/quarter/month/daytype in the
Quarter in the
levelName of the field in the
After this, filter the last months of the quarters.
Here is a sample for illustration: http://jsfiddle.net/flexmonster/6x4rjk5a/.
To clarify, Flexmonster provides an option to filter a particular month, however, the component cannot find the last month of the quarter by itself. Therefore, months should be filtered as in the example above.
Please note that all mentioned filter options can also be defined via the setFilter() API call.
Let us know if the suggested approaches would work for you.
We are looking forward to your response.
Thank you for the answer and example.
I would like to extend my question, because we have a bit more complex case. In our example we have a three years. Query last month would return the last month of the all 3 years, but that is not what do we need. In case that we manually define the months, we would have good values for the quarters but the year would be again their sum, not the last value.
We need to see on the chart value for each year (which is the value of the last month in year). So, our initial chart would contain three values – 2019 (value: 16 604 908), 2020 – (value: 17 410 692), and 2021 (value: 17 410 714).
When we have these three values correctly displayed, the next step would be to have the possibility to drill down, each year, to the quarters (the value of the quarter is the value of the last month within the quarter).
Can you give us an advice for the solution in the case of more then one year?
Thank you in advance.
Thank you for providing further information about your case.
We would like to explain that a parent level of the hierarchy can show only the aggregation of its children’s values.
Therefore, it’s impossible for a year which contains 4 quarters to show the value of one quarter.
The best solution for this case is to create a mechanism that will change the slice according to user’s needs.
Please have a look at the example we prepared for you: http://jsfiddle.net/flexmonster/k9qs58fg/.
In the context menu, we added two items, which are responsible for changing the chart. The click on the “Drill down” button will show the data by quarters, “Drill up” – by years.
More details about customizing the context menu can be found here: https://www.flexmonster.com/api/customizecontextmenu/.
Do not hesitate to contact us if you have any questions regarding the example.
Also, please let us know if the suggested approach would work for you.
Thank you a lot for the answer and provided example. This is exactly what we were looking for.
There is one small question left, how can we customize labels for the quarterly report? You can see how does it look like now in the attachment. It would be nice to see also the corresponding year maybe below the quarters.
Is that possible?
We are glad to hear that our answer helped.
Our team would like to confirm that it is possible to customize labels on the chart.
For this purpose, Flexmonster provides customizeChartElement API call.
Please see the example we have prepared for you: http://jsfiddle.net/flexmonster/tuyLxzcg/.
customizeChartElementFunction (lines 209-215), we took the year from the
data object and added it to the
Let us know if the provided example would work for you.
We are reaching out to ask if the provided approach works well for you.
Looking forward to your feedback.
Thank you a lot for the provided answers and examples. It helped us.