topic Re: Using a filter with on activate sheet trigger in QlikView

Using a filter with on activate sheet trigger

zagzebski — Sat, 19 Jul 2014 19:00:39 GMT

I am trying to filter some data based on the activation of a sheet (using a trigger)

Right now I have a bunch of expressions that look similar to this:

Sum({$<MajorCategory={'Operating Expense'},FilterUser={'$(=OSUser())'}>} Net)

I want to get able to take this section: FilterUser={'$(=OSUser())'} of the expression and use it to filter the data upon activation of the sheet. Is this possible? This part of the set analysis filters on the user's data so I have to put it into every expression if I don't figure out a way to get it at the sheet level. (Note: I realize this is like section access but for different reasons section access is not the best route)

Thanks in advance.

Re: Using a filter with on activate sheet trigger

israrkhan — Sat, 19 Jul 2014 21:06:45 GMT

you can create a variable , and assign the user name to that variable,

then in the script load, you can write condition on your column, like

where columnname = $(variable)..

Re: Using a filter with on activate sheet trigger

zagzebski — Sat, 19 Jul 2014 21:31:20 GMT

Thanks for the response!

Unfortunately I don't think that will work. Some of the tabs are filtered (like in my example). However some of the tabs should not be filtered by user. If I put where clause in script then it would eliminate needed data for some of the tabs.

Re: Using a filter with on activate sheet trigger

Sat, 19 Jul 2014 22:15:23 GMT

Did you try using a "select in field" trigger ?

You can set the Field as FilterUser and in search string you provide the osuser().

Re: Using a filter with on activate sheet trigger

zagzebski — Sun, 20 Jul 2014 00:59:02 GMT

Yes - I want to use a trigger but it is a little more complicated than your suggestion. Here is the structure

FilterUser = STEPP

OSUser() = STEPP

DEPARTMENT: IT, SALE, FINANCE

DEPARTMENTs specifically defined in the data that point to STEPP = IT and SALES

I would like to use the "select in field" so that the field is DEPARTMENT. However I don't know how to write the search string. In the search string how do I create the relationship between FilterUser=OSUser()and bring back only possible DEPARTMENTS (IT and Sales).

Re: Re: Using a filter with on activate sheet trigger

felipe_dutra — Sun, 20 Jul 2014 02:05:26 GMT

Look at the example!

Re: Re: Using a filter with on activate sheet trigger

zagzebski — Sun, 20 Jul 2014 14:06:26 GMT

Thanks for the response.

Not sure I understand the example.

Where does it evaluate (compare) the OSUser to the FilterUser so you know who is currently logged on?

Also - Not sure I am follow your "select in field" expression.

Re: Re: Using a filter with on activate sheet trigger

zagzebski — Sun, 20 Jul 2014 14:14:13 GMT

Somehow the trigger has to use the OSUser function to compare to the FilterUser field. It can't be a manual process to select a FilterUser.

Re: Re: Re: Using a filter with on activate sheet trigger

felipe_dutra — Sun, 20 Jul 2014 15:54:39 GMT

look this:

Re: Using a filter with on activate sheet trigger

felipe_dutra — Sun, 20 Jul 2014 16:01:13 GMT

You need to change the script, run, save, close and open the application again.

Re: Using a filter with on activate sheet trigger

zagzebski — Sun, 20 Jul 2014 16:13:22 GMT

Awesome! I have to do a little more testing but I think this is exactly what I need!

Thanks so much!

Re: Using a filter with on activate sheet trigger

felipe_dutra — Sun, 20 Jul 2014 16:18:45 GMT