8 Replies Latest reply: Mar 7, 2016 4:20 PM by Sunny Talwar RSS

    Ignore Field

    Cassandra Baqir

      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

          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
              Cassandra Baqir

              As always, thank you sir.

              • Re: Ignore Field
                Cassandra Baqir

                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

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

                      • Re: Ignore Field
                        Cassandra Baqir

                        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

                            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