topic Re: Ignore Field in QlikView

Ignore Field

cbaqir — Mon, 07 Mar 2016 20:11:47 GMT

I need to ignore selections made to FISCAL_YEAR and MONTH_1 in this expression. For some reason, it works for FISCAL_YEAR but not for MONTH_1.

round(Sum({1<FISCAL_YEAR=,MONTH_1=,APP_DATE={">$(=Max({<ACT_AMT={'>0'},CTGY_CODE>}APP_DATE))"}, FCST_DATE = {"$(=date(max({<CTGY_CODE>}FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000

It makes no difference if I use round(Sum({$ instead...

I have another expression that this works perfectly: round(sum({$<FISCAL_YEAR=,MONTH_1=>} ACT_AMT), 1000)/1000

Any ideas?

Thanks!

Re: Ignore Field

sunny_talwar — Mon, 07 Mar 2016 20:14:03 GMT

May be this:

Round(Sum({1<FISCAL_YEAR, MONTH_1, APP_DATE = {">$(=Max({<ACT_AMT={'>0'}, CTGY_CODE, FISCAL_YEAR, MONTH_1>}APP_DATE))"}, FCST_DATE = {"$(=Date(Max({<CTGY_CODE, FISCAL_YEAR, MONTH_1>} FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000

Re: Ignore Field

cbaqir — Mon, 07 Mar 2016 20:21:49 GMT

As always, thank you sir.

Re: Ignore Field

cbaqir — Mon, 07 Mar 2016 20:38:32 GMT

Any suggestion for how to handle a dimension like this: =if(isnull(REQ_NAME),REQUEST_ID,REQ_ID_NAME)

round(Sum({$<MONTH_1=,FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000

Re: Ignore Field

sunny_talwar — Mon, 07 Mar 2016 20:40:05 GMT

Not sure I understand what you mean when you say handle a dimension like this? Can you elaborate a little bit?

Re: Ignore Field

cbaqir — Mon, 07 Mar 2016 20:44:47 GMT

In the first example, the Dimension from the chart is CTGY_CODE:

Round(Sum({1<FISCAL_YEAR, MONTH_1, APP_DATE = {">$(=Max({<ACT_AMT={'>0'}, CTGY_CODE, FISCAL_YEAR, MONTH_1>}APP_DATE))"}, FCST_DATE = {"$(=Date(Max({<CTGY_CODE, FISCAL_YEAR, MONTH_1>} FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000

In the second, the Dimension from the chart is an IF statement: =if(isnull(REQ_NAME),REQUEST_ID,REQ_ID_NAME)

round(Sum({$<MONTH_1=,FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000

Re: Ignore Field

sunny_talwar — Mon, 07 Mar 2016 21:00:07 GMT

and you want to be able to ignore selection in them?

May be this:

Round(Sum({$<MONTH_1= ,FISCAL_YEAR= , REQUEST_ID, REQ_ID_NAME, APP_DATE={">$(=Max({<ACT_AMT={'>0'}, REQUEST_ID, REQ_ID_NAME>}APP_DATE))"}, FCST_DATE = {"$(=Date(Max({<REQUEST_ID, REQ_ID_NAME>} FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000

Re: Ignore Field

cbaqir — Mon, 07 Mar 2016 21:18:37 GMT

I think I got it using something similar, thanks:

Round(Sum({$<MONTH_1= ,FISCAL_YEAR= , REQ_NAME, REQUEST_ID, REQ_ID_NAME, APP_DATE={">$(=Max({<ACT_AMT={'>0'}, MONTH_1= ,FISCAL_YEAR= , REQ_NAME, REQUEST_ID, REQ_ID_NAME>}APP_DATE))"}, FCST_DATE = {"$(=Date(Max({<MONTH_1= ,FISCAL_YEAR= , REQ_NAME, REQUEST_ID, REQ_ID_NAME>} FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000

Re: Ignore Field

sunny_talwar — Mon, 07 Mar 2016 21:20:37 GMT

Awesome