Actions in a button

[jduenyas]

Hello all:

On a sheet I have 2 buttons with 2 actions each.

The first button has 2 actions
       A) Clear All --  (This clears the sheet from any pre-existing selections anywhere)   and followed by
       B) Select In Field set for Year and Search String is set to  =Year(today())  (This selects the current year)
This works fine no matter what selections are present in the sheet. Eveything is cleared out and the current year is selected.

The second button has 2 actions
      A) Clear All --  and followed by
      B) Select In Field is set for MonthYear (this is a field in my calendar in the form of Mmm YYYY like Sep 2015). 
            In the Search String I have this Concat function:
          = '("' & Concat( DISTINCT  If(MonthYear>=MonthName(AddMonths(Today(),-12))  AND  MonthYear<=MonthName(AddMonths(Today(),-1)), MonthYear) , '"|"' , MonthYear)  & '")'

This selects the last 12 rolling months

And here are the problems:

--- If there is nothing pre-selected anywhere on the sheet, it works just fine. But if there is a month (or a group of months) already selected they are NOT cleared by the Clear All action in A.
--- If there is anything else pre-selected in the sheet, other than month(s), everything will be cleared out but it will require a second click to then select the 12 rolling months.

Does anyone know why is that? Is there a way to activate the button and dynamically select 12 rolling months?

(*** Note  -  My calendar contains dates also into the future (next 12 months) for forcasting purposes. The action of the first button described above selects the current year and ALL months of the year Jan - Dec are associated. The action of the second button described selects specifically just the 12 rolling months.
Also, the delimiter between the MonthYear values is set to " (double quotation mark (chr(34)) on either side of the Pipe. If setting to other delimiter such as Asterisk or no delimiter, it does not work at all )

As usual, your help is appreciated.

Thanks

Josh

[jduenyas]

Is there no one who can answer this?

[ecolomer]

check if the lock option is activated

to ensure "Clear all" to work you must first "unlock" everything and then "Clear all"

[sunny_talwar]

Can you give this a shot:

='("' & Concat(DISTINCT {1<Date = {"$(='>=' & MonthStart(AddMonths(Today(), -12)) & '<=' & MonthEnd(AddMonths(Today(), -1)))"}>} MonthYear , '"|"') & '")'

[sunny_talwar]

Just make sure that you see the same format for your date field when you paste this -> ='>=' & MonthStart(AddMonths(Today(), -12)) & '<=' & MonthEnd(AddMonths(Today(), -1)) in a text box. If you don't see the same format, force it using Date in your set analysis:

='("' & Concat(DISTINCT {1<Date = {"$(='>=' & Date(MonthStart(AddMonths(Today(), -12)), 'YourDateFormat') & '<=' & Date(MonthEnd(AddMonths(Today(), -1)), 'YourDateFormat'))"}>} MonthYear , '"|"') & '")'

[maxgro]

try to split the second button in 2 different button (1 with clear all, the other with select in field) to check if the single action work

[jduenyas]

Yes, Massimo. It does work with 2 separate buttons. When nothing else is pre selected on the sheet it works fine.

The purpose of this button is to help users bring the sheet to a starting point if they made too many selections and need to quickly go back to square one.

The string returned by the Concat function for this month is this:

("Sep 2014"|"Oct 2014"|"Nov 2014"|"Dec 2014"|"Jan 2015"|"Feb 2015"|"Mar 2015"|"Apr 2015"|"May 2015"|"Jun 2015"|"Jul 2015"|"Aug 2015")

When putting this string in an Advanced Search it works regardless of what the previous selection was in the listbox

.

[jduenyas]

Sunny, thank you for your reply.

I tried that with Set Analysis format but that does not work at all.

The Concat function and Set Analysis do not work together  with this format of date MMM YYYY which is needed if I want to get "rolling months" and which is returned by the built in function MonthName() .

[jduenyas]

Thanks but that did not work either.

[maxgro]

could you post a small .qvw with the problem?