topic Re: Determining if first item in set is selected in QlikView

Determining if first item in set is selected

Thu, 30 May 2013 22:41:26 GMT

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.

Re: Determining if first item in set is selected

swuehl — Thu, 30 May 2013 23:59:42 GMT

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')

Re: Determining if first item in set is selected

Fri, 31 May 2013 00:10:59 GMT

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

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

Re: Determining if first item in set is selected

swuehl — Fri, 31 May 2013 10:07:20 GMT

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')

Re: Determining if first item in set is selected

Fri, 31 May 2013 14:05:28 GMT

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

Re: Determining if first item in set is selected

swuehl — Fri, 31 May 2013 16:11:40 GMT

{<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.

Re: Determining if first item in set is selected

Fri, 31 May 2013 16:16:15 GMT

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