Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
Specialist II

## Ignore Field

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!

1 Solution

Accepted Solutions
MVP

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

8 Replies
MVP

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

Specialist II
Author

As always, thank you sir.

Specialist II
Author

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

MVP

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

Specialist II
Author

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

MVP

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

Specialist II
Author

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

MVP

Awesome

Community Browser