topic Re: Actions in a button in QlikView

Actions in a button

jduenyas — Fri, 11 Sep 2015 22:23:08 GMT

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

Re: Actions in a button

jduenyas — Sat, 12 Sep 2015 04:51:52 GMT

Is there no one who can answer this?

Re: Actions in a button

ecolomer — Sat, 12 Sep 2015 04:57:21 GMT

check if the lock option is activated

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

Re: Actions in a button

sunny_talwar — Sat, 12 Sep 2015 05:25:28 GMT

Can you give this a shot:

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

Re: Actions in a button

sunny_talwar — Sat, 12 Sep 2015 05:28:07 GMT

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 , '"|"') & '")'

Re: Actions in a button

maxgro — Sat, 12 Sep 2015 08:36:27 GMT

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

Re: Actions in a button

jduenyas — Sat, 12 Sep 2015 14:14:24 GMT

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

Re: Actions in a button

jduenyas — Sat, 12 Sep 2015 14:21:55 GMT

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() .

Re: Actions in a button

jduenyas — Sat, 12 Sep 2015 14:22:34 GMT

Thanks but that did not work either.

Re: Actions in a button

maxgro — Sat, 12 Sep 2015 14:37:19 GMT

could you post a small .qvw with the problem?

Re: Actions in a button

jduenyas — Sat, 12 Sep 2015 15:05:00 GMT

Here it is attached.

---If you clear everything and then click the button 12 months will be selected.

---If you select one or more of the selected months in the MonthYear list box and click the button nothing will happen.

---If you Clear All (With the Menu Clear button), select a month or 2 and then click the button, the selection will be cleared and require a second click to select the 12 months.

The Clear All does not seem to work or the Concat functions does not refresh the selection.

Thanks Massimo

Re: Actions in a button

jduenyas — Sat, 12 Sep 2015 15:21:59 GMT

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

Re: Actions in a button

evan_kurowski — Sat, 12 Sep 2015 17:02:03 GMT

Hello Josh,

Here is a variation that allows a sliding selection of the starting point of the 12-month window (while forcing all selections of future ranges to start from the present)

Re: Actions in a button

jduenyas — Sat, 12 Sep 2015 20:47:17 GMT

I like it!

Thanks Evan