Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Set Analysis: exclude selections on a single field only

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

Tags (1)
7 Replies
bbi_mba_76
Valued Contributor

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

Hi,

have you tried to replace the 1 with $ ?

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

Employee
Employee

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

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

MVP
MVP

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

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
israrkhan
Valued Contributor II

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

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

Not applicable

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

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!

israrkhan
Valued Contributor II

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

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



Not applicable

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

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.

Community Browser