Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

antoniodneto
Contributor

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

Re: Help Expression

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))
14 Replies
Khan_Mohammed
Honored Contributor II

Re: Help Expression

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

Re: Help Expression

Hi Antonio,

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

antoniodneto
Contributor

Re: Help Expression

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
Contributor

Re: Help Expression

Hi Shahbaz,

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

Tks for helping!!

Khan_Mohammed
Honored Contributor II

Re: Help Expression

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
Contributor

Re: Help Expression

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
Contributor

Re: Help Expression

Hi Arvin,

Still can help me?

 

Tks!

MVP
MVP

Re: Help Expression

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

Re: Help Expression

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