Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

IAMDV
Honored Contributor II

Set Analysis with Variable

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis with Variable

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

4 Replies
MVP
MVP

Re: Set Analysis with Variable

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
Honored Contributor II

Re: Set Analysis with Variable

Thanks Stefan.

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

Many thanks again.

Cheers,

DV

Not applicable

Re: Set Analysis with Variable

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
Honored Contributor II

Re: Set Analysis with Variable

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

Community Browser