Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Determining if first item in set is selected

I have a Title Expression that changes based on selection of field EVENT_NUMBER.  If no selections, then title includes 'ALL' in text displayed, otherwise it displays 'TOP' with a selected count.  This is fine as long as the first through n EVENTS are selected.  Below is the expression to accomplish this.

 

=IF(IsNull(EVENT_NUMBER), ' '

     & If(GetSelectedCount(EVENT_NUMBER) = 0

              ,'ALL'

              , 'TOP ' & GetSelectedCount(EVENT_NUMBER)) & ' EVENTS LAST 30 DAYS', ' ' & 'EVENT: ' & EVENT_NUMBER
    
)

I would like to display 'VARIOUS EVENTS LAST 30 DAYS' if a non-sequential set of Events are selected or if a sequential set not starting from the top.

1 Solution

Accepted Solutions
MVP
MVP

Re: Determining if first item in set is selected

Should be possible to use the rank instead:

if(GetSelectedCount(EVENT_NUMBER) = Max(aggr(rank(sum({<EVENT_NUMBER=>} EVENT_COUNT),4,1), EVENT_NUMBER)), 'Sequential from Top','Non sequential from Top')

6 Replies
MVP
MVP

Re: Determining if first item in set is selected

If your EVENT_NUMBER field shows integer numbers from 1 to X, and top means 1, you can try like this:

=if(GetSelectedCount(EVENT_NUMBER)=Max(EVENT_NUMBER),'Sequential from Top','Non sequential or not starting from Top')

Not applicable

Re: Determining if first item in set is selected

EVENT_NUMBERS are not sequential but are sorted by rank in my straight table, i.e.

 

    num(rank(sum(EVENT_COUNT),4,1))

MVP
MVP

Re: Determining if first item in set is selected

Should be possible to use the rank instead:

if(GetSelectedCount(EVENT_NUMBER) = Max(aggr(rank(sum({<EVENT_NUMBER=>} EVENT_COUNT),4,1), EVENT_NUMBER)), 'Sequential from Top','Non sequential from Top')

Not applicable

Re: Determining if first item in set is selected

Hi swuehl,

Thanks for this Expression, it works perfectly!  That being said, not so sure I understand the syntax and how it actually works ...not sure what the curly backets functionality is?   I greatly appreciate you help.

Best Regards,

Lew

MVP
MVP

Re: Determining if first item in set is selected

{<EVENT_NUMBER= >}

is set analysis syntax. It says 'ignore selection in EVENT_NUMBER when calculating the aggregation I am embedded in' (i.e. the sum(EVENT_COUNT) ).

You need to ignore the selection, otherwise the rank will always be determined on the selected subset of EVENT_NUMBER (while you need the global rank, not affected by selection, right?).

The max( aggr( rank(...), EVENT_NUMBER)) is then just calculating the max rank out of your selected EVENT_NUMBER (here, it is important to only look at the selected items, so no set analysis part required here.

Not applicable

Re: Determining if first item in set is selected

swuehl,

Very much appreciated!  As I've not had any formal set analysis training I have had to pick this stuff up as I go. Your explanation helps tremendously. Glad there are so many folks such as yourself who have been quick to answer questions as they arise.

I've learned more about QV from the QV Community than any other source.

Cheers,

Lew

Community Browser