🍉 Meet Flexmonster Pivot Table & Charts 2.9.Check out all hot features!
Get Free Trial

How to filter the specific month (take the data from the last month of the period?)

Re-Open
Randolph Kepplinger asked on May 17, 2021

Hi!
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: 

  1. Do you have an example where you filter specific month in the initialization?
  2. Do you have an example of the calculated field which is taking only the data from last month of the period? (In case of the yearly report the last month would be December, except in case of current year when the last month is the previous month) 

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,
Kind regards.
 

10 answers

Public
Randolph Kepplinger May 17, 2021

Also how can we use dates in formulas, e.g. do something when month is 3,6,9,12

Public
Milena Pechura Milena Pechura Flexmonster May 18, 2021

Hello, Randolph,
 
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.
 
Best regards,
Milena

Public
Randolph Kepplinger May 19, 2021

Hello Milena,
Thank you for the answer. 
We are providing you a JSFiddle with our data sample (just a subset of the data).
http://jsfiddle.net/cushrLdo/100/
Hope that this will help you to understand our problem better.
Kind regards. 

Public
Milena Pechura Milena Pechura Flexmonster May 20, 2021

Hi, Randolph!
 
Thank you for preparing the example.
Please find our suggestions below.
 

  1. To filter a specific month, the filter object should be specified for the field in the slice. Here is a code snippet for your reference:
    "slice": {
    "columns": [
    {
    "uniqueName": "Datum",
    "levelName": "Datum.Year",
    "filter": {
    "members": [
    "datum.[2019].[december]"
    ]
    }
    }
    ],
    ...
    }

    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.

  2. For the last month of the current year, our "Last..." filter can be used: http://jsfiddle.net/flexmonster/m8s2whcg/.
    Please read the details about conditional filters for dates here: https://www.flexmonster.com/api/date-query-object/.
    To specify this filter using UI, click "Datum" -> "Filter by dates" -> "Last..." and "Month".
  3. To show the data by quarters, define the year/quarter/month/day type in the mapping:
    "mapping": {
    "Datum": {
    "type": "year/quarter/month/day"
    }
    }

    Then specify Quarter in the levelName of the field in the slice:

    "columns": [
    {
    "uniqueName": "Datum",
    "levelName": "Datum.Quarter"
    }
    ]

    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.
 
Kind regards,
Milena

Public
Randolph Kepplinger May 25, 2021

Hello Milena, 
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.
Regards.
 
 

Public
Milena Pechura Milena Pechura Flexmonster May 26, 2021

Hello, Randolph,
 
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.
 
Best regards,
Milena

Public
Randolph Kepplinger May 28, 2021

Hello Milena, 
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?
Kind regards.

Attachments:
report.png

Public
Milena Pechura Milena Pechura Flexmonster June 1, 2021

Hello, Randolph,
 
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/.
In the customizeChartElementFunction (lines 209-215), we took the year from the data object and added it to the element.
 
Let us know if the provided example would work for you.
 
Best regards,
Milena

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster June 9, 2021

Hello, Randolph,
 
We are reaching out to ask if the provided approach works well for you.
 
Looking forward to your feedback.
 
Regards,
Illia

Public
Randolph Kepplinger 2 days ago

Hello Illia,
Thank you a lot for the provided answers and examples. It helped us.
Kind regards.

Please login or Register to Submit Answer