Ignore Field

[cbaqir]

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!

[sunny_talwar · Accepted solution]

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

[cbaqir]

As always, thank you sir.

[cbaqir]

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

[sunny_talwar]

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

[cbaqir]

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

[sunny_talwar]

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

[cbaqir]

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

[sunny_talwar]

Awesome