topic Re: Set Analysis: exclude selections on a single field only in QlikView

Set Analysis: exclude selections on a single field only

Tue, 08 Oct 2013 14:03:18 GMT

I'm putting together a profile of various course-level student statistics, which requires the selection of a single 'acacdemic year', and allows for optional selection across other data fields (e.g. subject, level of study, location of study). The table therefore displays a set of statistics for a single acacdemic year.

What I also want to do is to draw in a couple of statistics for the previous academic year, whilst maintaining any of the optional selections that a user might make.

At the moment I've got as far as this:

count( {1<[AYR_BEGY] = {$(=Only([AYR_BEGY])-1)}>} [STU_CODE] )

This works fine until (as expected) I make another selection on one of the optional fields (e.g. subject) because it continues to always show the full set of data for the previous year.

Is there a way of altering the statement such that it only ignores selections on the AYR_BEGY field but takes into account all other selections?

Thanks

Re: Set Analysis: exclude selections on a single field only

bbi_mba_76 — Tue, 08 Oct 2013 14:07:25 GMT

Hi,

have you tried to replace the 1 with $ ?

count( {$<[AYR_BEGY] = {$(=Only([AYR_BEGY])-1)}>} [STU_CODE] )

Re: Set Analysis: exclude selections on a single field only

Clever_Anjos — Tue, 08 Oct 2013 14:15:51 GMT

count( {<[AYR_BEGY] = }>} [STU_CODE] )

Re: Set Analysis: exclude selections on a single field only

jonathandienst — Tue, 08 Oct 2013 14:17:15 GMT

I am not sure if I have understood you correctly, but to ignore selections in AYR_BEGY while respecting all others, just use:

count({<[AYR_BEGY]=>} [STU_CODE])

Regards

Jonathan

Re: Set Analysis: exclude selections on a single field only

israrkhan — Tue, 08 Oct 2013 14:19:30 GMT

you wrote: Is there a way of altering the statement such that it only ignores selections on the AYR_BEGY field but takes into account all other selections?

then try below:

=count({<[AYR_BEGY] = {$(=Only([AYR_BEGY])-1)}>} [STU_CODE] )

Re: Set Analysis: exclude selections on a single field only

Tue, 08 Oct 2013 14:47:27 GMT

Sorry, I wasn't very clear - I need it to take account of the selection in AYR_BEGY (the selected year), because it needs to return the results for [AYR_BEGY]-1 (the previous year), but by having the '1' at the front of the set analysis statement it then also ignores the selections applied to any other fields, which I don't want it to do.

Using the $ instead of (or missing out) the 1 returns zero for [AYR_BEGY]-1 because the selection is restricted to [AYR_BEGY] only.

Thanks for your quick reponses though!

Re: Set Analysis: exclude selections on a single field only

israrkhan — Tue, 08 Oct 2013 14:56:01 GMT

Try Below

=count({<[AYR_BEGY] = {$(=Max([AYR_BEGY])-1)}, FirstField= , SecondField=, Third= >} [STU_CODE] )

Replace FirstField, SecondField and Third with your Fields, in which you dont want selections..

hope it helps

Re: Set Analysis: exclude selections on a single field only

Fri, 11 Oct 2013 08:13:10 GMT

Thanks for your suggestions - I've got round it like this:

if(count([STU_CODE])>0,count( {1<[AYR_BEGY] = {$(=Only([AYR_BEGY])-1)}>} [STU_CODE] ))

so it only provides the statistic where the course is present in the current selection, but maintains any selections applied to other fields.