Help Expression

[antoniodneto]

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!!!

[MK9885]

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?

[afroz_shaik]

Hi Antonio,

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

[antoniodneto]

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!

[antoniodneto]

Hi Shahbaz,

Take a look in another example below that i'm trying to solve!!

Tks for helping!!

[MK9885]

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

[antoniodneto]

Hi Arvin,

Still not working.

I have a dinamic table with DATA_BASE (201810,201809) as Dimension.

For my measure I'm using 

if(DATA_BASE_FINAL = MAX(DATA_BASE_FINAL),sum({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 1'},MOTIVO_2={'Amortiza Saldo'},DATA_BASE_FINAL={'$(=MAX(DATA_BASE_FINAL))'}>}SDB_ATUAL)/1000000
,
IF(DATA_BASE_FINAL = MAX(DATA_BASE_FINAL,2),sum({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 1'},MOTIVO_2={'Amortiza Saldo'},DATA_BASE_FINAL={'$(=MAX({1}DATA_BASE_FINAL))'}>}SDB_ANT)/1000000
,
0))

My table is just returning the first condition. In 201809 I need to show SUM(SDB_ANT) from 201810.

Tks for help!

[antoniodneto]

Hi Arvin,

Still can help me?

Tks!

[sunny_talwar]

You want to show 201810 number in 201809 dimension? Which part of the expression is calculating 201810 number?

[antoniodneto]

Yeah Exactly that!

My dinamic table is something like that

                               201809                     201810

Measure     SUM(SDB_ANT)    SUM(SDB_ATUAL)

So In 201809 I need to use SDB_ANT of 201810

I'm trying to use my expression below: 

if(DATA_BASE_FINAL = MAX(DATA_BASE_FINAL),sum({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 1'},MOTIVO_2={'Amortiza Saldo'},DATA_BASE_FINAL={'$(=MAX(DATA_BASE_FINAL))'}>}SDB_ATUAL)/1000000
,
IF(DATA_BASE_FINAL = MAX(DATA_BASE_FINAL,2),sum({<MACRO={'MESMO ESTAGIO'},MOTIVO={'Continua Stage 1'},MOTIVO_2={'Amortiza Saldo'},DATA_BASE_FINAL={'$(=MAX(DATA_BASE_FINAL))'}>}SDB_ANT)/1000000
,
0))

Is not working for the second condition, My table only displays 201810 value and 201809 as 0