topic Re: Set Analysis with Variable in QlikView

Set Analysis with Variable

IAMDV — Sat, 09 Jun 2012 13:15:03 GMT

Dear All,

Quick question on Set Analysis. I'm struggling to think and I need some help.

I'm trying to use the Variable in Set Analysis which contains below expression :

ONLY(ID)

And my Set Analysis Expression is :

=SUM({$<Part = {">$(=($(vTest)))"} >} MyCount)

And this is returning 0 instead it should return same values as [Matching Output] column - (Please see attached example).

I already achieved the final result in [Matching Ouput] field but when the users select the [Part] field then my numbers change and I don't them to change but there should be selection on the chart/table. That's reason I'm using the Set Analysis approach instead of using Above() and RangeSum() functions.

I hope this makes sense.

Thanks in advance.

Cheers,

Re: Set Analysis with Variable

swuehl — Sat, 09 Jun 2012 14:01:06 GMT

Hi DV,

it seems to me that you are trying to create a set expression that will evaluate the ID in the context of the current line, and I believe this will not work. The set expression will be evaluate once per chart (I think you can actually see the expression used when using <use expression> as label and then hover over the label in your chart column).

There are methods to use a pick / match combination to simulate an evaluation per current dimension value (by creating in fact a set expression per line), check out the discussion thread here)

Coming back to your original request, if I understood correctly, you can achieve what you want by embedding your working expression (with an appropriate set expression added) in an advanced aggregation, like

=aggr(SUM({1}TOTAL MyCount) - RangeSum(Above(sum({1}MyCount),1,RowNo()) ),Part)

Re: Set Analysis with Variable

IAMDV — Sat, 09 Jun 2012 14:58:51 GMT

Thanks Stefan.

This makes sense. I keep forgetting about Set Analysis behaviour and end-up wasting my time.

Many thanks again.

Cheers,

Re: Set Analysis with Variable

Sat, 09 Jun 2012 21:18:48 GMT

Thanks to both of you, to Mr. DV for asking such a question and to Mr. Swuehl a wonderful answer and for such a nice thread.

One of my senior Mr. Anil (user name: anilsknp) me about both of you that some time Mr. DV and alot of time Mr. Swuehl : Stefan help him.

Very nice and logical explanation Mr. Stefan headsoff to you.

DV can you please explain in a very simple way again that "How to ask a question in community"

thanks & regards

Indian Qv Lover

Re: Set Analysis with Variable

IAMDV — Sat, 09 Jun 2012 22:02:36 GMT

indianqvlover - I'm glad to know that you have learnt something new. Thanks to Stefan.

Stefan - I have decided to use Pick/Match approach along with Concat() & ValueLoop() functions to generate the expression based on Dimension. Thank you for all your help.

Cheers,