Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sheet-Level Set Analysis?

Hiya Guys,

Is it possible to apply Set Analysis (or similar) to a specific Sheet?

For example, if you had a Dashboard for a market stall which has data on all available fruit and veg - would it be possible to apply an "Apples Only" Set Analysis-style filter on one Sheet - so all previous variables could be used, but applying only to "Apples"?... otherwise I am going to have to create a whole new set of variables just for this one specific sheet - which would be complicated and prone to user-error.

Any information anyone can provide will be greatly appreciated.

Cheers,

Steve.

1 Solution

Accepted Solutions
Alexander_Thor
Employee
Employee

Perhaps a OnActivateSheet trigger for the sheet that selects "Apples" for you , lock that selection.

Clear selection and unlock the field as a OnLeaveSheet trigger.

Not very elegant as it really locks down your users but atleast you don't get the performance hit of introducing a if statement for every expression.

View solution in original post

8 Replies
Not applicable
Author

I'm guessing no one knows?!  🙂

I have been looking into the "aggr" function - as this seems to create temporary tables' to work with... but not entirely sure of it's workings.  Does "aggr" ignore current Selections (ie: like {1} within Set Analysis?) or would a current selection effect the data returned?

Hopefully by asking this extra question it will "bump" this post and get more views!!!  🙂

Cheers,

Steve.

Anonymous
Not applicable
Author

Steve,

There is no relation between current sheet and the data.  Sheet is just a space for the objects.  But - you can retrieve current sheet ID using macro, and store it in a variable.  After that, you can use it any way you want, e.g. as a condition in expressions (set or no set).

Regards,

Michael

Not applicable
Author

Thanks Michael,

So, that would mean updating all of my Expressions with an "If" statement which will, if the "Apples" Sheet is active, perform Set Analysis for just "Apples" - otherwise use my original Expression?

Seems quite convoluted (as does everything within QlikView!) - I might have a play with it and see if I can get it to work.  I may also suggest a development to the QV Tech guys which would allow the "filtering" functionality I suggested (above)... would you agree that this would be a useful development in the future?

On a similar note - is this, effectively, how the "aggr" function works?

Cheers,

Steve.

Alexander_Thor
Employee
Employee

Perhaps a OnActivateSheet trigger for the sheet that selects "Apples" for you , lock that selection.

Clear selection and unlock the field as a OnLeaveSheet trigger.

Not very elegant as it really locks down your users but atleast you don't get the performance hit of introducing a if statement for every expression.

Not applicable
Author

Thanks Alexander,

As you say - not very elegant, but it would defitely do the trick for Sheet-Level filtering (the problem would be to remember the previous Selection so it could be re-applied when "OnLeaveSheet" triggers).

As an aside - what would be the best solution for, for example, a chart just based on just "Apples"? (regardless of the current Selection) - with other Objects being attached to the Selection?  Set Analysis using {1}?  Or possibly the "aggr" function?

I'm struggling to see the use of the "aggr" function...

Cheers,

Steve.

Alexander_Thor
Employee
Employee

Well, you should be able to clear a single field e.g the one containing apple and leaving the other selections intact.

Disregarding the current selections with Set Analysis is the way to go atleast performance wise.

If you are running QV11 I would also adivse that you look into alternate states. That would allow you to break your application/data into more states then the $ (default state) we are used to and possibly create a very confusing mess for the users .

If you wanna force a product selection just do a Sum({<Product = {Apple}>} SalesAmount) but as you say you would have to move away from your variables as this is a deviation from your orginal expression... makes sense though

For aggr check out the "Nested Aggregations and Related Issues" chapter in the help file, it describes a very common scenario where aggr comes into play.

Not applicable
Author

Thanks again Alexander - very helpful!

When I get chance I'll do some more reading  🙂  It's difficult juggling the balance between learning the best way/how to develop something - and then ACTUALLY developing it!  🙂

I've marked your previous answer as "Correct" - as I think that's going to be as close as we're gonna get!  🙂

Cheers,

Steve.

Alexander_Thor
Employee
Employee

Usually when I end up in this situation I try to rethink the approach instead.

If the goal is to prevent the user from accidentally selecting more then one product visual guidence goes a long way together with the hide exluded option in the listbox.

The Asset Management demo under the Investment Profile has a good example on this, layering a semi-transparent text object with some help indication and layout conditions. This keeps the expressions clean and allows the user to to investment analysis without the fear of having them select something that is wrong according to the data model.

Edit: Forgot the url to the demo: http://eu.demo.qlik.com/QvAJAXZfc/opendoc.htm?document=qvdocs/Asset%20Management.qvw&host=Demo11