Set Analysis with Variable

[IAMDV]

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,

DV

[swuehl · Accepted solution]

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)

See also attached,

Stefan

[IAMDV]

Thanks Stefan.

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

Many thanks again.

Cheers,

DV

[#4]

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

[IAMDV]

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,

DV

www.QlikShare.com