How to create WTD, MTD, YTD, ALL Date Filter Buttons

[siddharth sabat]

Hi,

Is there a way to create date filtered buttons like WTD, MTD, YTD and upon clicking on those the analysis will get filtered with that corresponding button selection.

There are formulas to create custom filters for WTD, MTD and YTD with boolean values. In this case, its kind of extra values for users to select True and False check boxes to filter the data.

WTD: Week([Date]) = week(DateTimeNow()) and Year([Date]) = year(DateTimeNow())

MTD: Month([Date]) = month(DateTimeNow()) and Year([Date]) = year(DateTimeNow())

YTD: Year([Date]) = Year(DateTimeNow())

But a date filtered button, itwould be a much easier functionality from end user point of view. Please suggest if any way to achive that. I have attached the screenshot for reference of desied buttons.

Thanks

Sidharth

[Jose Leviaguirre]

Hellosiddharthsabat,

Attached find an example that limits the data of a visualization via expressions through document properties. There is another example that changes the date range filter programatically when a document property changes.

Using expressions is straight forward.

Today [Date]=Date(DateTimeNow())

Last 30 Days [Date] >= dateadd('dd',-30,DateTimeNow())

This Week Week([Date]) = week(DateTimeNow()) and Year([Date]) = year(DateTimeNow())

This Month Month([Date]) = month(DateTimeNow()) and Year([Date]) = year(DateTimeNow())

Year to Date Year([Date]) = Year(DateTimeNow())

Last Month Month([Date]) = Month(dateadd('mm',-1,DateTimeNow())) and Year([Date]) = Year(dateadd('mm',-1,DateTimeNow()))

Last 12 Months [Date] >= dateadd('mm',-12,DateTimeNow())

Q1 Quarter([Date]) = 1 and Year([Date]) = Year(DateTimeNow())

Q2 Quarter([Date]) = 2 and Year([Date]) = Year(DateTimeNow())

Q3 Quarter([Date]) = 3 and Year([Date]) = Year(DateTimeNow())

Q4 Quarter([Date]) = 4 and Year([Date]) = Year(DateTimeNow())

ALL 1=1Using Iron python script to change the Date Range Filter programatically is a bit trickier since it has to find the actual values to filter, but the logic is all there:

from Spotfire.Dxp.Application.Filters import RangeFilter, ValueRange

from System import DateTime

 

dateFilterName="Date"

#option="Last 12 Months"

 

d1,d2=DateTime.Now,DateTime.Now

 

if (option=="Today"):

d1 = d2# + d.date

if (option=="Last 30 Days"):

d1 = d2.AddDays(-30);

if (option=="This Week"):

d1 = d2.AddDays(-7 if int(DateTime.Now.Date.DayOfWeek)==0 else -int(DateTime.Now.Date.DayOfWeek));

if (option=="This Month"):

d1 = DateTime(d2.Year, d2.Month, 1).Date;

if (option=="Year to Date"):

d1 = DateTime(d2.Year, 1, 1).Date;

if (option=="Last Month"):

d2 = DateTime(d1.Year, d1.Month, 1).Date.AddDays(-1);

d1 = DateTime(d2.Year,d2.Month,1).Date;

if (option=="Last 12 Months"):

d1 = d2.AddMonths(-12);

 

print "Ideal dates: t",d1,"t",d2

 

 

filt=Document.FilteringSchemes[0].Item[dt].Item[dt.Columns.Item[dateFilterName]].As[RangeFilter]()

filt.Reset()

 

#find lower and upper bounds from column

lb,ub=set(),set()

 

rows = Document.ActiveFilteringSelectionReference.GetSelection(dt).AsIndexSet()

 

for r in rows:

v=dt.Columns["Date"].RowValues.GetValue®.Value

if v.Date>=d1.Date:lb.add(v)

if v.Date

[siddharth sabat]

Many thanks @Jose for the detailed code and explanantion. It worked great for me. 

 

Also I figured out another way to directly call an Iron Python script. Basically I created an action control of type button and pointed that to a iron python script and then after clicking on the button its bascally filters out the data. The script basically accepts no of days and data table as arguments to fileter the data.

 

Thanks

 

Sidharth

 

 

 

 

[Shahrukh Choudhary]

Hi @siddharth sabat​ 

Could you please share the details on you have implemented the other solution?