topic Re: sum in set analysis not resolving '>=$(v_YTM_MONTH)<=$(v_FTM_MONTH)' in QlikView

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

Fri, 07 Aug 2015 10:01:24 GMT

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)

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

marcus_sommer — Fri, 07 Aug 2015 10:10:49 GMT

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

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

lironbaram — Fri, 07 Aug 2015 10:11:10 GMT

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

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

sasiparupudi1 — Fri, 07 Aug 2015 10:27:56 GMT

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

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

sasiparupudi1 — Fri, 07 Aug 2015 10:39:26 GMT

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