Set Analysis Expression - Multiple GetFieldSelections()

Yi0ta4 — Wed, 31 Mar 2021 17:41:09 GMT

Hello,

I was wondering if anyone would be able to help me with the following issue.

I am trying to write an expression using set analysis that will return records based on selected date field values.

Here is the expression I wrote:
count({$
<[Sale Close Year]=
,[Purchase Year]={'$(=getFieldSelections([Sale Close Year]))'}
,[Purchase Quarter]={'$(=getFieldSelections([Sale Close Quarter]))'}
,[Purchase Month]={'$(=getFieldSelections([Sale Close Month]))'}
>}
[CUST_ACCT_NB])

(Note: I use '[Sale Close Year ]=' to exclude this filter from my result set, this is because I am counting Purchase records that have a [Purchase Year] that falls into the same year as the selected [Sale Close Year ] but its associated [Sale Close Year] falls into a different year.... due to timing issues in record processing.)

The expression above works if only one of each date field component is selected.
However, it is possible for a user to select multiple values for any of these date fields: [Sale Close Year],[Sale Close Quarter],[Sale Close Month]
I need to write an expression that can handle any selection combination.

Here is an image of the selection filters:

Since the expression changes needed should apply to all of the date field components, lets simplify the problem by looking at just one of the fields, [Sale Close Month] for example.

This expression works if only one [Sale Close Month] is selected:
[Purchase Month]={'$(=getFieldSelections([Sale Close Month]))'}

To test this, I wrote the following expression in a text box:
=if(GetSelectedCount([Sale Close Month])>0,getFieldSelections([Sale Close Month]))
It returns:
Jan,Feb,Mar
So I am assuming that is what the getFieldSelections function is returning.
(Note: I use the conditional expression to check to see if the user has made a selection on this field, and handle it if that check is true.)

Since these are string values I know I will have to quote each one, so I wrote:
=if(GetSelectedCount([Sale Close Month])>0,chr(39) & getFieldSelections([Sale Close Month]) & chr(39))
But this returns:
'Jan,Feb,Mar'

Questions:

1.) Do any of you know of a way to quote each value selected?
2.) If so, how would I incorporate the modification into my original expression so it works in the count function?
(I get confused on whether to place modifications within the single quotes, or outside of them.)

Example:

[Purchase Month]={'chr(39) & $(=getFieldSelections([Sale Close Month])) & chr(39)'}

vs.

[Purchase Month]={chr(39) & '$(=getFieldSelections([Sale Close Month]))' & chr(39)}

Neither of these work.

Thanks in advance for any help you can provide!

Re: Set Analysis Expression - Multiple GetFieldSelections()

GaryGiles — Wed, 31 Mar 2021 19:21:19 GMT

You can something like this:

[Purchase Year]={$(=concat(distinct chr(39)&[Sale Close Year]&chr(39),','))}

Re: Set Analysis Expression - Multiple GetFieldSelections()

Yi0ta4 — Wed, 31 Mar 2021 19:40:16 GMT

That works perfectly... Thanks Gary!! 😎

topic Set Analysis Expression - Multiple GetFieldSelections() in QlikView

Set Analysis Expression - Multiple GetFieldSelections()

Re: Set Analysis Expression - Multiple GetFieldSelections()

Re: Set Analysis Expression - Multiple GetFieldSelections()