Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
antoniodneto
Creator II
Creator II

Help Expression

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

1 Solution

Accepted Solutions
sunny_talwar

How about this

If(DATA_BASE_FINAL = Max(TOTAL 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(TOTAL DATA_BASE_FINAL,2), Sum(TOTAL {<MACRO = {'MESMO ESTAGIO'}, MOTIVO = {'Continua Stage 1'}, MOTIVO_2 = {'Amortiza Saldo'}, DATA_BASE_FINAL={'$(=MAX(DATA_BASE_FINAL))'}>} SDB_ANT)/1000000, 0))

View solution in original post

14 Replies
MK9885
Master II
Master II

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
Contributor III
Contributor III

Hi Antonio,

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

antoniodneto
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Hi Shahbaz,

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

Tks for helping!!

MK9885
Master II
Master II

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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