3 Replies Latest reply: Nov 8, 2018 2:00 PM by Shahbaz Khan Mohammed RSS

    Help Expression

    Antonio Domingos

      Hi Guys,

       

      I have the following field STAGE with their values Stage 1, Stage 2 and Stage 3.

       

      I'm using the expression =SUM({<MACRO={'MESMO ESTAGIO'},DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000


      But I need to create an IF condition with the field MOTIVO.

       

      It would be something like IF STAGE = Stage 1 do SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 1'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000 IF STAGE = Stage 2 SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 2'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000


      Depending on field STAGE the expression with the field MOTIVO should change.

       

      Can you help me?

       

      Tks a lot!!!

        • Re: Help Expression
          Shahbaz Khan Mohammed

          I guess your explanation itself is correct, it just need correct syntax.


          IF (STAGE = 'Stage 1', SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 1'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000,

          IF (STAGE = 'Stage 2', SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 2'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000,

          IF (STAGE = 'Stage 3' ,SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 3'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000))))

           

          this would work only if you select Stage 1 or Stage 2 or Stage 3 but if you do not select anything it may show blank chart.

          So for default or w/o any selection you may want to set it up as

           

          if(Getselectedcount(STAGE)=0,

          SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 1'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000,

          IF (STAGE = 'Stage 1', SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 1'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000,

          IF (STAGE = 'Stage 2', SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 2'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000,

          IF (STAGE = 'Stage 3' ,SUM({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 3'}DATA_BASE_FINAL={"$(=MAX(DATA_BASE_FINAL))"}>}SDB_ATUAL)/1000000)))))

           

          Hopefully this would work?

          • Re: Help Expression
            Afroz Shaik

            Hi Antonio,

             

            As of now i will suggest you to use PICK MATCH functions, this will be the easy to implement.

              • Re: Help Expression
                Antonio Domingos

                Hi Afroz, follow another example that i'm trying to do.

                 

                I have a pivot table with my dimension DATA_BASE_FINAL:

                 

                                              201809          201810

                Stage 1                    2.608            2.731

                 

                My expression is =sum({<Stage_IFRS9_ATUAL={'Stage 1'},MACRO={'NOVAS OPERAÇÕES'}>}SDB_ATUAL)/1000000


                But for 201809 I should use =SUM(SDB_ANT)

                It would be something like:

                 

                IF(DATA_BASE_FINAL) = 201809 DO sum({<Stage_IFRS9_ATUAL={'Stage 1'},MACRO={'NOVAS OPERAÇÕES'},DATA_BASE_FINAL={201810}>}SDB_ANT)/1000000

                 

                IF(DATA_BASE_FINAL) = 201810 DO sum({<Stage_IFRS9_ATUAL={'Stage 1'},MACRO={'NOVAS OPERAÇÕES'},DATA_BASE_FINAL={201810}>}SDB_ATUAL)/1000000

                 

                But into a pivot table.

                 

                It helps? LOL

                 

                Tks!

                  • Re: Help Expression
                    Shahbaz Khan Mohammed

                    Maybe this?

                     

                    IF(DATA_BASE_FINAL = '201809', sum({<Stage_IFRS9_ATUAL={'Stage 1'},MACRO={'NOVAS OPERAÇÕES'},DATA_BASE_FINAL={201810}>}SDB_ANT)/1000000,

                     

                    IF(DATA_BASE_FINAL = '201810' , sum({<Stage_IFRS9_ATUAL={'Stage 1'},MACRO={'NOVAS OPERAÇÕES'},DATA_BASE_FINAL={201810}>}SDB_ATUAL)/1000000))

                     

                    Considering DATA_BASE_FINAL, Stage_IFRS9_ATUAL, MACRO are Dimensions in your Data Model.

                    again this would work only when you select either 201809 or 201810