sum in set analysis not resolving '>=$(v_YTM_MONTH)<=$(v_FTM_MONTH)'

[#1]

Help Qlik Guru's,

Pls help me to resolve the proper sum as the set analysis not resolving.'>=$(v_YTM_MONTH)<=$(v_FTM_MONTH)'

If i use '$(v_FTM_MONTH)' then it resolved properly.

=Sum({<KTEXT_EQ={'EXTERNAL'}>}{<EQDATE_EQ= {'>=$(v_YTM_MONTH)<=$(v_FTM_MONTH)'}>}FUEL_AMT_MONTH_EQ)

[marcus_sommer · Accepted solution]

There is a small syntax-issue and you need to exchange the single-quotes with double-quotes because it's a search-expression and to make sure that your variables return the excact same format as your field (you might need a foramat-function like date() for this):

=Sum({<KTEXT_EQ={'EXTERNAL'}>}, EQDATE_EQ= {">=$(v_YTM_MONTH)<=$(v_FTM_MONTH)"}>} FUEL_AMT_MONTH_EQ)

- Marcus

[lironbaram]

hi

you need to fix your formula to this one

=Sum({<KTEXT_EQ={'INTERNAL'},EQDATE_EQ= {'>=$(v_YTM_MONTH) <=$(v_FTM_MONTH)'}>}FUEL_AMT_MONTH_EQ)

also change the date format for v_YTM_MONTH to 'DD/MM/YYYY

[sasiparupudi1]

Sum({<KTEXT_EQ={'EXTERNAL'},EQDATE_EQ= {">=Date(Date#($(v_YTM_MONTH),'MMYYYY'),'MM/DD/YYYY')<=$(v_FTM_MONTH)"}>}FUEL_AMT_MONTH_EQ)

[sasiparupudi1]

I think there is a mistake in your variable

if(Num(Month)<=3,Date(MakeDate(Year-1,4,01),'DDMMYYYY'),Date(MakeDate(Year,4,01),'DDYYYY'))

should it be

if(Num(Month)<=3,Date(MakeDate(Year-1,4,01),'DDMMYYYY'),Date(MakeDate(Year,4,01),'DDMMYYYY')) ??

if so then your set analysis

Sum({<KTEXT_EQ={'EXTERNAL'},EQDATE_EQ= {">=Date(Date#($(v_YTM_MONTH),'DDMMYYYY'),'DD/MM/YYYY')<=$(v_FTM_MONTH)"}>}FUEL_AMT_MONTH_EQ)

and you may want to exclude the year and month selections as you would want to filter the data on EQDATE_EQ

Sum({<Year=,Month,KTEXT_EQ={'EXTERNAL'},EQDATE_EQ= {">=Date(Date#($(v_YTM_MONTH),'DDMMYYYY'),'DD/MM/YYYY')<=$(v_FTM_MONTH)"}>}FUEL_AMT_MONTH_EQ)

HTH

Sasi