Mutliple selection in getFieldSelections with Set analysis filters with set identifier = 1

[QGTFS]

Hello,

I'm having issue to fix the multiple selection with the field filtering with a Month to Date calculation, when selecting multiple values for the filters, the result is void. I've been trying to work around the getFieldSelections (see [ISSUE]=...), which as I've read causes issues with multi select, but without success.  

Ps, I cannot use the current selection ($<>), has I need to get information out of the filters in order to have the month to date sum work for previous times to see the state at said day.

Here's the short version of the code :

num(
 Sum(
  {1<[ISSUE]= {"$(= if( IsNull(GetFieldSelections([ISSUE]))
                     ,'*'
                     , chr(39)& Replace(GetFieldSelections([ISSUE])
			 , ',', chr(39) &','& chr(39)) & chr(39) 
		    )
		)"}
  >}
  [Amount]/$(='Rate')
  )
,'###,###,###,###.')

Here's the full version of my current expression:

num(
Sum(
{1
<
[Field] ={"$(= if( IsNull(GetFieldSelections([Field])),'*', GetFieldSelections([Field])))"} -P({$<[Field]={TEST}>} [Field])
,[FieldDate]={">=$(=
Num(MonthStart(

if(IsNull(GetFieldSelections([Year]))
, Max([FieldDate])
,if (isNull(GetFieldSelections([Month]))
,if(GetFieldSelections([Year]) = Year(today())
, Max([FieldDate])
,Num(Date('31/12/'& GetFieldSelections([Year]),'dd/MM/yyyy'))
)
,Num(DATE(DATE#(
if ( isNull(GetFieldSelections([Day]))
,Day(MonthEnd(Date(Date#('01/' & GetFieldSelections([Month]) & '/' & GetFieldSelections([Year]),'dd/MMM/yyyy'))))
,Max(GetFieldSelections([Day]))
)
& '/' & GetFieldSelections([Month]) & '/' & GetFieldSelections([Year]),'dd/MMM/yyyy')))
)
)

))
)<=$(=
if(IsNull(GetFieldSelections([Year]))
, Max([FieldDate])
,if (isNull(GetFieldSelections([Month]))
,if(GetFieldSelections([Year]) = Year(today())
, Max([FieldDate])
,Num(Date('31/12/'& GetFieldSelections([Year]),'dd/MM/yyyy'))
)
,Num(DATE(DATE#(
if ( isNull(GetFieldSelections([Day]))
,Day(MonthEnd(Date(Date#('01/' & GetFieldSelections([Month]) & '/' & GetFieldSelections([Year]),'dd/MMM/yyyy'))))
,Max(GetFieldSelections([Day]))
)
& '/' & GetFieldSelections([Month]) & '/' & GetFieldSelections([Year]),'dd/MMM/yyyy')))
)
)
)"}
,[Field Val] ={"$(= if( IsNull(GetFieldSelections([Field Val])),'*', GetFieldSelections([Field Val])))"}
,[Field1]= {"$(= if( IsNull(GetFieldSelections([Field1])),'*', GetFieldSelections([Field1])))"}
,[Field2] = {"$(= if( IsNull(GetFieldSelections([Field2])),'*', GetFieldSelections([Field2])))"}
,[ISSUE]= {"$(= if( IsNull(GetFieldSelections([ISSUE])),'*', chr(39)& Replace(GetFieldSelections([ISSUE]), ',', chr(39) &','& chr(39)) & chr(39) ))"}

>
}
[Amount]/$(='Rate')
)
,'###,###,###,###.')

I would really appreciate some help over this thorn !

Have a great day !

[marcus_sommer]

I suggest strongly to simplify the entire logic and not using any getfieldselections() or getselectedcount() respectively yours isnull(getfieldselections()) within a set analysis.

Instead of getfieldselections() I would use p() and if not the current selection-state should be considered I would overwrite it within the p() like p({1} Field) or p({< F1, F2 >} Field). By the FieldDate assigning I would outsource the logic into a variable by simplifying the logic because many of the getfieldselections() aren't needed, for example the first part could be replaced by:

if(isnull(Year) or Year = year(today()), max(FieldDate), ...

also the various date(), num() and the combining of dates could be made more simple - maybe with makedate().

[QGTFS]

Thank you for you answer, you're right this code could be simplified a little. I'll try using the P() to  change the way it's currently working.

[QGTFS]

After doing some testing, both solution with overwriting and the one with P  don't  seem apply in this case, I might have misunderstood or not been clear. The first issue at hand is FieldDate, which is determined by filters on [Day], [Month], [Year]. The FieldDate is then used to sum the value, from month to date. Unfortunately, with version with, or without P(), overwriting, the displayed sum does a month to date sum fine as long as the [Day] is not selected, once selected, instead of showing Month to Date, it just displays the value of the chosen [Day].

In a attempt to get around this restriction, I'm trying to recompose the user's selection and alter it for this field. The script I've published works as long as only one value at most is selected for each filter.  The issue is when more than one is selected, then the filter breaks. Is there another way to get the values, so that multiple values are correctly taken into account ?

[marcus_sommer]

In many scenarios it's quite helpful not to make a bunch of trials within the origin application else to create a dummy application with just a few small inline tables and with a few records - and then increasing step by step more tables/associations and records. Everything would be performed in real-time and any results of the low number of records/field-values could be apprehended at one glance - working or not and mostly if not the expected results is returned it's obvious what was calculated. Finding and fixing errors in logic like above shown will nearly always cost more efforts ...

Beside this if I understand your answer right is the aim of your selection-query of day/month/year to switch between YTD, MTD, Today and similar measures it might be useful to transfer the essential part into script by flagging this information within the master-calendar. A lot of background to this topic could you find here:

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741

[QGTFS]

Hi Marcus, thanks for your time and advice.

Unfortunately your link redirects afterward to archived articles which  are no longer available.

I'll try another way.

[QGTFS · Accepted solution]

In the end I found a way around using a variable :

=if(IsNull(GetFieldSelections([ISSUE])),chr(39)&'*' & chr(39) , concat( distinct chr(39)&replace(GetFieldSelections([ISSUE]),', ',chr(39)&','&chr(39))&chr(39), ','))

I then use the variable directly in the filter:  [ISSUE]= {$(vCurrentISSUE)}

The limit to this method is it's restricted to do all calculation inside the variable. Having a filter like {"$(= $(vCurrentISSUE))"} will not resolve as expected, might be an issue of mix of single and double quotes.