topic Re: Exclude sheet from field selection in QlikView

Exclude sheet from field selection

Mon, 09 Sep 2013 18:51:32 GMT

What is the simplest way to exclude an entire sheet from a single field's selection? Ex. All field selections in the document apply to the sheet and it's child objects as normal except for the Year field selections.

Re: Exclude sheet from field selection

Anonymous — Mon, 09 Sep 2013 19:39:43 GMT

Hi,

There is two way to exclude the filter selection.

1. Use island table (no link/relation with any table)

2. Use set analysis. ex(=sum({<productCat= >}nbr))

Regards

Ashok

Re: Exclude sheet from field selection

Mon, 09 Sep 2013 19:45:07 GMT

Thanks for the reply Ashok, but unfortunately, your suggestion won't work for this case. What I'm trying to achieve is to have all normal selections applied to the objects on a sheet except for year. I've tried {<Year=>}, but it doesn't work because, when a year is selected, it can narrow down the months that are available for selection. Basically, I'm trying to keep all months available for selection no matter what year is selected. Does that make sense?

Re: Exclude sheet from field selection

Anonymous — Mon, 09 Sep 2013 19:53:23 GMT

try like this =sum({<Year=,Month=,Quarter=,Week=,Day= >}amount)

Re: Exclude sheet from field selection

Mon, 09 Sep 2013 20:30:22 GMT

Hi Adam,

How you months are being displayed. If they are 'Jan', 'Feb' etc then use

sum({<Year=,Month=p(Month)>}amount)

If they are 2013-Jan, 2013-Feb etc then the possible option is to use alternate state.

Make a new state and assign the Year selection to the new state and it will only affect in that state.

Hope this help,

Anosh

Re: Exclude sheet from field selection

Tue, 10 Sep 2013 15:18:03 GMT

That didn't seem to work either. I've included my expression in hopes that it will make more sense:

Sum({$<qtMonth={$(=chr(39) & Concat(distinct Month, chr(39) & ',' & chr(39)) & chr(39))}>} qtEntCore)

I have a spreadsheet that loads qtMonth and qtEntCore. It is something like this:

salesPerson, qtMonth, qtEntCore

Joe, Jan, 50

Joe, Feb, 100

Joe, Mar, 120

etc.....

When no Month is selected on screen, it should sum all qtMonths for a given sales person. Problem is, if someone was to select a Year, it may narrow down the Months to less than all 12 of them, which would lessen qtEntCore.

I have, actually, come up with a sort of solution for this. I've removed the Year selection from the sheet. I added an onActivate event for the sheet that saves any pre-selected Year to a variable and then clears Year. Onleave, I add the Year selection back from the variable. I know this isn't the greatest solution, but it seems to work for what I need.

Thanks again for your help.

Re: Exclude sheet from field selection

CELAMBARASAN — Tue, 10 Sep 2013 15:39:45 GMT

Hi,

You can try with the new QV 11 feature Alternate states.

Have the sheet objects in one alternate state and other sheet objects are in default state.

Re: Exclude sheet from field selection

Tue, 10 Sep 2013 15:44:17 GMT

Thanks for the reply Celambarasn, but I have considered Alternate States. The problem is, I need all other document selections to apply to the sheet except for one field. I can't have the sheet as an island.

Re: Exclude sheet from field selection

Tue, 10 Sep 2013 18:08:35 GMT

Hi Adam,

Try using only this in your expression

Sum({$<year=>} qtEntCore)

Hope this helps,

Anosh

Re: Exclude sheet from field selection

Tue, 10 Sep 2013 18:35:15 GMT

Yep, tried that first :

Sum({$<Year=,qtMonth={$(=chr(39) & Concat(distinct Month, chr(39) & ',' & chr(39)) & chr(39))}>}qtEntCore)

Also tried:

Sum({1<[Fiscal Year]=> * $<qtMonth={$(=chr(39) & Concat(distinct Month, chr(39) & ',' & chr(39)) & chr(39))}>}qtEntCore)

Unfortunately, no dice

Re: Exclude sheet from field selection

Tue, 10 Sep 2013 20:27:12 GMT

Hi Adam, Don't put month constrain in your expression. If you don't say it

by default takes all month. Sinch you are concatenating all months values

it is only concatenate only those months which comes under selected year.

To check this put this in a text box and check what all months are getting

appended: $(=chr(39) & Concat(distinct Month, chr(39) & ',' & chr(39)) &

chr(39))

Re: Exclude sheet from field selection

Tue, 10 Sep 2013 20:46:51 GMT

Yep, therein lies the problem. I still need the ability for the user to select a Month on the sheet to only return qtEntCore for equivalent qtMonth(s).

salesPerson, qtMonth, qtEntCore

Joe, Jan, 50

Joe, Feb, 100

Joe, Mar, 120

etc.....

So, if the user selects Jan + Feb for Joe, it will return 150. But if no Month is selected, it would return qtEntCore for all months for Joe. I realize this a crazy way to relate the months. Ideally, they should be associated by the same field name. But I tried and tried and wasn't able to get my data model to work out that way. Hence the set analysis to return the equivalent month's value.

Re: Exclude sheet from field selection

rbecher — Tue, 10 Sep 2013 21:50:46 GMT

I'm a bit late here but have you tried 'alternate states'? I think this could be a solution.

- Ralf

Re: Exclude sheet from field selection

Thu, 12 Sep 2013 15:22:33 GMT

Yes sir, I have.

"Thanks for the reply Celambarasn, but I have considered Alternate States. The problem is, I need all other document selections to apply to the sheet except for one field. I can't have the sheet as an island."

Thank you for the reply though.

Re: Exclude sheet from field selection

Thu, 12 Sep 2013 19:37:27 GMT

Hi Adam, Can you create some dummy data and create a qvw to explain your

scenario and put it here, So that I can have a look at it.

Re: Exclude sheet from field selection

CELAMBARASAN — Thu, 12 Sep 2013 23:00:02 GMT

It is only possible with the use of set anlaysis then .

You need to write field exclusion in all the expressions used in that sheet.

Could you post the sample file?