Help with Expression

[antoniodneto]

Hi Guys, 

Could you guys give some help with my issue?

Is something, my date field ANOMES the values is 201812,201811,201810,201809.

I'm trying to calculate the variation of two months.

My expression is sum({<STAGE_IFRS9_FINAL_X={'Stage 1'},Carteira_IFRS_ATUAL={'EMPRESAS_1','EMPRESAS_2','EMPRESAS_3'},MOTIVO=-{'Fora Escopo Anterior','Saiu do Escopo','Se mantém fora do Escopo','Fora Escopo Atual'},DATA_BASE_FINAL={'$(=MAX(DATA_BASE_FINAL))'}>}EAD_ATUAL)/1000000 that brings me the value for 201812.

sum({<STAGE_IFRS9_FINAL_X={'Stage 1'},Carteira_IFRS_ATUAL={'EMPRESAS_1','EMPRESAS_2','EMPRESAS_3'},MOTIVO=-{'Fora Escopo Anterior','Saiu do Escopo','Se mantém fora do Escopo','Fora Escopo Atual'},DATA_BASE_FINAL={'$(=MAX(DATA_BASE_FINAL,2))'}>}EAD_ATUAL)/1000000 bring me the value for 201811.

When I calculate 201812 and 201811 the expression works, but when I select an another month for example 201811, the first expression works, but the second supposed to show me 201810 value, however doesn't work.

Tks a lot!

[sunny_talwar · Accepted solution]

Try this for the second expression

DATA_BASE_FINAL = {"$(=Max({1<ANOMES = {[<=$(=Max(ANOMES))]}>}DATA_BASE_FINAL, 2))"}

instead of

DATA_BASE_FINAL = {'$(=Max(DATA_BASE_FINAL,2))'}

[antoniodneto]

Hi Sunny, you save me as usual.

That works right!

One doubt, Can I combine the sum of field and this variation into a same graph?

[sunny_talwar]

What do you mean? Not sure I understand the question.

[antoniodneto]

How Can I display it in a Line Chart this expression, and another measure. Will be a Line Chart with two lines.

The expression works in a KPI or a table, but when I use with Line Chart or a combined chat seems not display correctly.

[sunny_talwar]

Would you be able to share a sample so that we can check this out?

[antoniodneto]

I'm not!

My company have blocked to export.

[antoniodneto]

Sunny,

Look the image.

The first KPI is the variation, the second is the max data = 201812 and third max data -2 = 201811. When I use KPIs the expression works even when I select any month.

The Line Chart, blue line is just the sum of value not using the max data, to show the value of each month.

The red line is the expression to calculate variation, I don't know but not display the same value like the first KPI. 

[antoniodneto]

Sunny,

I tried using the expression with ABOVE 

=sum({$<STAGE_IFRS9_FINAL_X={'Stage 1'},Carteira_IFRS_ATUAL={'EMPRESAS_1','EMPRESAS_2','EMPRESAS_3'},MOTIVO=-{'Fora Escopo Anterior','Saiu do Escopo','Se mantém fora do Escopo','Fora Escopo Atual'},DATA_BASE_FINAL={"$(=(MAX(DATA_BASE_FINAL)))"} >} EAD_ATUAL)/1000000
-
above(sum({$<STAGE_IFRS9_FINAL_X={'Stage 1'},Carteira_IFRS_ATUAL={'EMPRESAS_1','EMPRESAS_2','EMPRESAS_3'},MOTIVO=-{'Fora Escopo Anterior','Saiu do Escopo','Se mantém fora do Escopo','Fora Escopo Atual'},DATA_BASE_FINAL = {"$(=Max({1<DATA_BASE_FINAL = {[<=$(=Max(DATA_BASE_FINAL))]}>}DATA_BASE_FINAL, 2))"} >} EAD_ATUAL))/1000000

For the max date = 201812, returns a second bar with the value of variance, but for the other months no.