topic AW:Multiple GetFieldSelections in QlikView

Multiple GetFieldSelections

Wed, 23 Feb 2011 15:47:55 GMT

Hi,

I have a Calendar of Months and Days and I would like to use Set Analyzer to filter based on the selected Months OR Days or Combination. As follow:

=sum( {1< MONTH = {$(=GetFieldSelections(SelectedMonth))} , DAY = {$(=GetFieldSelections(selectedDay))} >} SALE )

The problem is that I have results only when I have selections in both the Months and the Days. I get Zero if I select the Months or the Days ONLY.

Could you advise?

Thanks

AW:Multiple GetFieldSelections

Wed, 23 Feb 2011 15:53:28 GMT

Hi Fabio,

this is because GetFieldSelections will return an empty result if no selection is made. Maybe GetPossibleValues works out better in your case.

cheers

Florian

AW:Multiple GetFieldSelections

Wed, 23 Feb 2011 16:04:06 GMT

Hi Florian,

THanks for your reply. I cannot find GetPossibleValues function as a valid funcion to use or something silirar. Could you help?

THanks

AW:Re: AW:Multiple GetFieldSelections

Wed, 23 Feb 2011 16:11:55 GMT

Hi Fabian,

you're right. That was the one function I was always looking for but it doesn't exist. I wonder why you work with a set analysis here. Is there no relationship between your date selection and sales?

You could put an IF around your expression to check if there is any selection (GetSelectionCount()>0) and if yes, you apply GetFieldSelections(). Otherwise you leave this one blank - maybe you need to return a "*" to the set analysis.

cheers

Florian

AW:Re: AW:Multiple GetFieldSelections

Wed, 23 Feb 2011 16:17:03 GMT

Hi Florian,

I have 2 calendars, one with relation, and the other independent to do compare. My objective is to compare sales values in different periods and that is why I have 2 Calendars.

I did try using If but I have a hard time using IF within a Set Analyzer.

Thanks,

AW:Re: AW:Re: AW:Multiple GetFieldSelections

Wed, 23 Feb 2011 16:39:51 GMT

Hi Fabio,

whenever expressions in set analysis become too complex for me, I moved them to a variable and used the variable in my set analysis instead. This makes your application easier to maintain anyway, as you may want to reuse your formula at some other point.

cheers

Florian

AW:Multiple GetFieldSelections

Wed, 23 Feb 2011 17:32:25 GMT

There is a function built into Set Analysis that does what GetPossibleValues would do. It's called P(). I don't know if this will do what you want though.

Sum({<FIELD=P(FIELD)>} Value)

In your case, you probably want to return the selected items when there is a selection, but return all when there is not. In these cases, I use a combination of GetSelectedCount() and GetFieldSelections().

If(GetSelectedCount(FIELD)>0, GetFieldSelections(FIELD), Chr(42))

Replace your GetFieldSelections function with that. It will return the GetFieldSelections if there are selections or an asterisk if there isn't (which will allow all values).

AW:Multiple GetFieldSelections

Wed, 23 Feb 2011 17:56:36 GMT

Thanks. But when for example I choose January and I don´t choose any day, I want only the values from january and not ALSO returning all possible days from the other selection.

AW:Multiple GetFieldSelections

Wed, 23 Feb 2011 17:58:27 GMT

That's what it does. Standard Set Analysis uses Ands to connect the clauses. Month=January And Day=* means all Days in January. The restriction on your Month selection would take care of the Day selections.