Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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)

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
marcus_sommer

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
Partner - Master III
Partner - Master III

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
Master III
Master III

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

sasiparupudi1
Master III
Master III

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