Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
jduenyas
Specialist
Specialist

Actions in a button

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

1 Solution

Accepted Solutions
jduenyas
Specialist
Specialist
Author

I have resolved the problem:

The previous Concat function was:

='("'& Concat(DISTINCT  if(MonthYear>=MonthName(AddMonths(today(),-12)) AND MonthYear<=MonthName(AddMonths(today(),-1)),MonthYear),'"|"',MonthYear) &'")'

I have added the {1} selection into it and now it works:

'("'& Concat(DISTINCT {1} if(MonthYear>=MonthName(AddMonths(today(),-12)) AND MonthYear<=MonthName(AddMonths(today(),-1)),MonthYear),'"|"',MonthYear) &'")'

Thank you all for investigating that.

Josh

View solution in original post

13 Replies
jduenyas
Specialist
Specialist
Author

Is there no one who can answer this?

ecolomer
Master II
Master II

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
MVP
MVP

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

Thanks but that did not work either.

maxgro
MVP
MVP

could you post a small .qvw with the problem?