Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Florian,
THanks for your reply. I cannot find GetPossibleValues function as a valid funcion to use or something silirar. Could you help?
THanks
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
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,
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
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).
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.
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.