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

[rahuldhomane]

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

[Benji_PaldiTeam]

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

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

[email protected] 

Paldi Solutions | Sisense Experts for Embedded Analytics Tools

[rahuldhomane]

I have requested access for the Blox template. Can you please approve it?

Thanks,

Rahul

[Benji_PaldiTeam]

Hi @rahuldhomane ,

I'm not the owner of this template but you can use this one instead.

Benji

[email protected] 

Paldi Solutions | Sisense Experts for Embedded Analytics Tools

[rahuldhomane]

I have used the code but the action RelativeDate is not creating any buttons on the widget. Can you please check once?

Thanks,

Rahul

[Harry · Accepted solution]

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
[email protected]
QBeeQ - Gold Implementation and Development Partner
www.qbeeq.pl

[rahuldhomane]

Hi Harish,

A big thank you man!

Really appreciate your help on this!

Thanks,

Rahul

[dim4a91]

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