How do I use a button to trigger date range in current selection box?

[Anonymous]

I have a line chart which defaults to displaying 36 months of data.  The data will grow as I add records this year to as many as 48 months.  I would like to add buttons on my sheet so that the end user can select to view only the last 12, last 24 or last 36 months of the data and have it automatically add those time periods to my current selections box.  Optimally, each button will clear the current date range from the selection box when pressed and add the appropriate new range.  I realize that you can just lasso the range in the chart that you would like, I am just trying to make it easy for my end user.  As the date range is changing every month, I need a dynamic solution.

[Roop]

Have something like the following in your expression:

=sum({< [Reporting Date] = {">$(=Date(max([Reporting Date])-vNumberofCols))"}>}Sales)

and set up a number of buttons that set the variable vNumberofCols to 36 or 24 or whatever the number of months you wish to see. You could also add this to a slider.

Hope this helps

[chrismarlow]

If you have control of the script I would add a calendar table linked to the date on the fact data & mark up the years (either by calendar year, or counting back a year from current date, depending on the need), then use that field in a list box (so you could more easily select all 1/2/3 years).

Alternatively I would add a many-to-many mapping of dates to table with the required ranges in & use that as selection option in a list box.

[Anonymous]

Certainly a good suggestion.  I could go all the way back to my SQL script and build a case statement.  Or I could use a mapping table in the load script based on todays date and date ranges.  I was hoping to just use an expression in the trigger field of the button.  Thanks for your input!

[Anonymous]

Not sure why there is a sum statement here.

My dimension is PMPM period and the range of values goes from Jan-2012 to Dec-2014.  The date formats are created in the load script and I certainly have the actual calendar dates that correspond to the ranges in a field in the same table.  MY PMPM table only has 36 values, which will grow to 48 by the end of the year and then reset back to 36 as I am only keeping the last 3 years plus current in the data model.

Based on this, do you have a revised version of your set analysis above for me to consider.  Perhaps with my field names?

Thanks again for your help

[Anonymous]

Assuming there are Dates in your application, not only Months:

Create a variable vLimit, and create a button to toggle it, with action something like this:
=if(vLimit=12,24, if(vLimit=24,36, if(vLimit=36,48,12)))

Next, you can use it in each expressions, or maybe better in calculated dimension (one place, not three):
aggr(only({<Date={"$(='>=' & date(MonthStart(today(),-1*vLimit)))"}>} "PMPM Period"),"PMPM Period")

[Anonymous · Accepted solution]

I have come up with a short term solution to my problem.  I am using the Search String and displaying only values that are in current year 2015 and last 12 months ( 2014 ).  I actually have 3 date fields that I want to all be synchronized.  An Island Table called PMPM Period, Claim Paid Period and Enrollment Period.  I am using the button to clear each field and then using the Search String to select.  I have created 4 buttons.  One to clear and one to set for 12, 24 and 36 months.  I just had to add *2013* and *2012* to the Search String.  I do plan to try some of the other suggestions to make the range dynamic, but this will solve my end users requirements for the near future.

Thanks to all for their timely responses

[Roop]

I have attached a solution which I believe is simpler still .... Not sure, just let me know

[Anonymous]

Rupert,

I like it.  I have a better sense of what you stated in your first post.  I definitely appreciate you creating a visual for me to see it more clearly.  I like the manual input box for a custom solution.

Thanks,

Mike