Set Analisys take only the MAX month selected

[danosoft]

Hi i have this set analisys to set my variable:

Set vFineMonthContrAlt = MonthEnd(MakeDate(GetFieldSelections(YEAR),Month(Date(Date#(GetFieldSelections(MONTH),'MM')))));

this take me the last day of my year month selected, for example if i select 2018 and 3 it will result: 31/03/2018

i wanna change it in this way:

if i select more months like 3,4,5   i want it result only the last day of the last month i selected, in this example so it must resutl: 30/05/2018

how can i set my variable for this?

thanks

[danosoft]

Sorry if i write again but i need to put in the context and in the context it not work, look:

Set vFineMonthContrMaxMonth = MonthEnd(MakeDate(GetFieldSelections(ANNOOUT,';','','Contrattualizzato'),Month(Date(Date#(rangemax($(=GetFieldSelections(MESEOUT,',','','Contrattualizzato'))),'MM')))));

when in the text box i write:   =$(vFineMonthContrMaxMonth)

[olivierrobin]

where do you code your set instruction ?

the formula is designed to be used in UI

[danosoft]

look the example i need to use my variable in the UI

[olivierrobin · Accepted solution]

ok I see the problem

as you declare it in a set instruction, Qlik tries to interpret the $( when you construct your variable

so you have to replace set by let and to disconnect the $ of the (

achieve this by doubling all single quotes

and .....

let vFineMonthContrAlt=

'MonthEnd(MakeDate(GetFieldSelections(ANNOOUT,'';'','''',''Contrattualizzato''),Month(Date(Date#(rangemax(' & '$' & '(=GetFieldSelections(MESEOUT,'','','''',''Contrattualizzato''))),''MM'')))))';

[danosoft]

but... can you explain to me why i need to enclosed it in $() for make rangemax work, and not only write GetfieldSelections withouth enclose in $()

thanks

[danosoft]

but why if i do this expression it make me an error:

let vFineMonthContrAltSCADMaxMonth = 'MonthEnd(MakeDate(GetFieldSelections(ANNOOUT,'';'','''',''Contrattualizzato''),Month(Date(Date#(rangemax(' & '$' & '(=GetFieldSelections(MESEOUT,'';'','''',''Contrattualizzato'')))+1,''MM'')))))';

[olivierrobin]

where do you have an error .? and which one

I think +1 is misplaced

check you formula to see where you have to put it

[olivierrobin]

i think that without $, the formula is evaluated at compile time

$ forces QV to interpret it at run time

[danosoft]

I see the problem is there :

let vFineMonthContrAltSCADMaxMonth = 'MonthEnd(MakeDate(GetFieldSelections(ANNOOUT,'';'','''',''Contrattualizzato''),Month(Date(Date#(rangemax(' & '$' & '(=GetFieldSelections(MESEOUT,'';'','''',''Contrattualizzato'')))+1,''MM'')))))';

it must be:

let vFineMonthContrAltSCADMaxMonth = 'MonthEnd(MakeDate(GetFieldSelections(ANNOOUT,'';'','''',''Contrattualizzato''),Month(Date(Date#(rangemax(' & '$' & '(=GetFieldSelections(MESEOUT,'','','''',''Contrattualizzato'')))+1,''MM'')))))';

why i need to replate this '';'' with '',''  if i use SET i need to write with '';''

[olivierrobin]

in all case, you need ','

look at rangemax syntax

its parameters are a list of values separated by commas