Need a special offer?Find out if your project fits.
+

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

Answered
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.
 

14 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/conditional-query-object/?target=date.
    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 June 14, 2021

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

Public
Randolph Kepplinger June 17, 2021

Hello Milena, hello Illia,
We have one more question remaining on this topic. Can you provide us with an example how we can drill down between years and quarters, but within the same measures.
For example: we have a yearly report with a measure Marktwert selected, if we change it to the quarterly report - it will be reset and the measure will be the default measure - in this case Anschaffungswert. How can we drill down without changing the selected measure, so that we just change the report types for the Marktwert? 
Thank you in advance.
Kind regards.
 
 

Public
Milena Pechura Milena Pechura Flexmonster June 18, 2021

Hi, Randolph,
 
Thank you for your question.
 
Our team has modified the following example according to your needs: http://jsfiddle.net/flexmonster/v89s5khz/. Please try to select the measure via UI, then drill down or up.
Find some explanations regarding the example below.
 
In the showQuarters and showYears functions (109 and 219 lines correspondingly), we took the selected measure using getOptions() API call and set a new report with this measure.
Please note that the selected measure is specified in the options.chart.activeMeasure object of the report:

"options": {
"viewType": "charts",
"chart": {
"activeMeasure": {
"uniqueName": "Marktwert",
"aggregation": "sum"
}
}
}

 
Let us know if our answer helped.
 
Kind regards,
Milena

Public
Milena Pechura Milena Pechura Flexmonster June 24, 2021

Hello, Randolph,
 
Hope you are doing well!
 
We were wondering whether our response was helpful.
Could you please let us know if our example worked for you?
 
Waiting for your answer.
 
Regards,
Milena

Public
Randolph Kepplinger June 25, 2021

Hello Milena,
Thank you for the answer and the example, it helped us to solve our case.
Kind regards,
Randolph
 

Please login or Register to Submit Answer