Forum Discussion

rahuldhomane's avatar
rahuldhomane
ETL
11-11-2022
Solved

How can we show previous 12 month on selecting a particular month in filter

Hi harikm007 ,

I want to show previous 12 months of data from a selection I make on a month filter. But the user should be able to change the month and the previous 12 month will get updated based on the selection

For e.g. If I select Dec-2022 then the column chart should show bars from Jan-2022 to Dec-2022.

If I select March-2022 then the column chart should show bars from April-2021 to March-2022.

Regards,

Rahul

dashboards & reporting
  • Harry's avatar
    Harry
    11-14-2022

    Hello rahuldhomane ,

    You can try below widget script to display previous 12 months of data from selected month.  Here update the variable 'monthFilterName' with name of your filter. 

    
widget.on("beforequery", function(se,ev) 
{
	var monthFilterName = "Months in Date of Joining"; 
	var numberofPrevMonths = 10
	
	monthFilter = ev.query.metadata.find(el => el.jaql.title == monthFilterName && el.panel == 'scope')
	
	if(monthFilter)
	{
		
		prevMonth = new Date(monthFilter.jaql.filter.members[0])
		for(i = 1; i < numberofPrevMonths; i++)
		{
  			prevMonth = new Date(prevMonth.setMonth(prevMonth.getMonth()-1))
			preMonthStr =  prevMonth.getFullYear() + '-' + ('0' + (prevMonth.getMonth()+1)).slice(-2) +  "-01T00:00:00" //prevMonth.toISOString();
			
			monthFilter.jaql.filter.members.unshift(preMonthStr)
		}
	}
	
});

     

    Also if you add Date Range Slider plugin, it will give you an option to select any date range in addition to previous 12 months.

    Please let me know if you have any questions

     

    Always here to help,
    Harry from QBeeQ

    QBeeQ - Gold Implementation and Development Partner
    www.qbeeq.pl

     

7 Replies

Replies have been turned off for this discussion
  • Benji_PaldiTeam's avatar
    Benji_PaldiTeam
    Data Pipeline
    11-13-2022

    Hi rahuldhomane ,

    This is actually a really common use case and you have several options on the table. I will present the approach of creating a dedicated date range filter in this case:

    1. You can use BloX to create action buttons that will set your date filter on the relative date frame as detailed here:

    https://support.sisense.com/kb/en/article/relative-date-filter-in-blox

    2. Another option is to create a dedicated "date range" dimension and determine a custom date frame as you wish.

    It looks really good when you use the "Wiser" filter in conjunction with the "date range" dimension so your end users can filter the previous 12 months from the dashboard UI:

    https://support.sisense.com/kb/en/article/display-values-based-on-selected-date-for-multiple-time-frames

    https://community-old.sisense.com/hc/en-us/community/posts/360003331234-Wiser-Filter-Widget

    3. You can use Paldi's "Date Range Filter" which has a built-in feature for creating custom date ranges.

    The benefit of using this ability with Paldi's plugin is that you don't need to modify your data model for this use case, everything is calculated "under the hood" of the plugin

    So in your case, you can create a relatively previous 12-month range and your viewers would be able to use this option from the dashboard UI as shown in the picture below: 

    Note:
    The first two options are free solutions, last option is a premium plugin.

    Let me know if you have any other questions and if you're up for a quick demo.

    Always here to help,

    Benji

    benji@paldi.solutions 

    Paldi Solutions | Sisense Experts for Embedded Analytics Tools

  • Harry's avatar
    Harry
    Cloud Apps
    11-14-2022

    Hello rahuldhomane ,

    You can try below widget script to display previous 12 months of data from selected month.  Here update the variable 'monthFilterName' with name of your filter. 

    
widget.on("beforequery", function(se,ev) 
{
	var monthFilterName = "Months in Date of Joining"; 
	var numberofPrevMonths = 10
	
	monthFilter = ev.query.metadata.find(el => el.jaql.title == monthFilterName && el.panel == 'scope')
	
	if(monthFilter)
	{
		
		prevMonth = new Date(monthFilter.jaql.filter.members[0])
		for(i = 1; i < numberofPrevMonths; i++)
		{
  			prevMonth = new Date(prevMonth.setMonth(prevMonth.getMonth()-1))
			preMonthStr =  prevMonth.getFullYear() + '-' + ('0' + (prevMonth.getMonth()+1)).slice(-2) +  "-01T00:00:00" //prevMonth.toISOString();
			
			monthFilter.jaql.filter.members.unshift(preMonthStr)
		}
	}
	
});

     

    Also if you add Date Range Slider plugin, it will give you an option to select any date range in addition to previous 12 months.

    Please let me know if you have any questions

     

    Always here to help,
    Harry from QBeeQ

    QBeeQ - Gold Implementation and Development Partner
    www.qbeeq.pl

     

    • rahuldhomane's avatar
      rahuldhomane
      ETL
      11-14-2022

      Hi Harish,

      A big thank you man!

      Really appreciate your help on this!

      Thanks,

      Rahul

    • dim4a91's avatar
      dim4a91
      Data Storage
      12-06-2022

      Harry Hello! This solution is great! But how do I change the code to have last 12 months by default when I open the report? For now it only works if the time frame selected only less then 12 months but if it is more than all months are being selected but I only need to show only last 12 months based on end date.