calculate expression from another expression

[narumonK]

hi everyone

I want to calculate  expression  from expression  as below

I want to calculate var(%) and position var(%) is side by side 2018 as below

please help!!

Thank You.

[jaibau1993]

Hi! You can reference expression columns using "Column" function. Said so, try yhe following:

(column(1)-column(2))/Column(2)

Thus I calculate that % using column position (note that if you change the order of the expressions you will have to rewrite this one)

Regards,

Jaime. 

[narumonK]

that means the position expression is next expression4 right ? i want to display the expression side by side year 2018

[jaibau1993 · Accepted solution]

Sorry, I did not understand your issue.

See the attached file. Building a chart as you need requires some steps:

1. Create an island table with as many "Codes" as dimensions you'll need. In the example there are two codes: year and variation
2. Use a calculated dimension like =Pick(Code,Year,'Variation') 
3. Create expressions similarly:

Pick(Code, 
      sum(Value), //Expression for year columns
      Sum({<Year = {2018}>} Value)-Sum({<Year = {2019}>} Value) //expression for "variation" column
)

Don't forget to sort the dimension by "Code". Output:

Hope it helps! Regards,

Jaime.

[narumonK]

Thank

But i have many year when i selected 2019 the display must show 2018 and 2019  then 

when i selected 2018 the display must show 2018 and 2017 

as below

please recommend 

[jaibau1993]

Yes, I supposed so but I made a functional example, I left the details for you 🙂

I usually create a variable vMaxYear with this content:

=Max(Year)

The expression you need will look something similar to:

Pick(Code, 
      sum(Value), //Expression for year columns
      Sum({<Year = {"$(=vMaxYear-1)"}>} Value)-Sum({<Year = {$(vMaxYear)}>} Value) //expression for "variation" column
)

But, as I said, with the info I have I can not go into the details.

Regards,

Jaime.

[narumonK]

That good!!

Thank.

I have any question 

VLclaim = 300000,

Vvalues =  'Prior Year','Report Year','Var(%)'

VpriorYY = Prior Year

VreportYY = Report Year

1. when i created expression for sum(paid)  by  ID have value >= 300000 

pick(match(ValueList($(Vvalues)),$(Vvalues)),
sum({$<T_ID_CLAIM_NO={"=sum(T_F1_AMOUNT_CLAIM_PAID)>=$(VLclaim)"},T_D0_CONSULTTING_YEAR={$(=VpriorYY)}>}T_F1_AMOUNT_CLAIM_PAID)

,

sum({$<T_ID_CLAIM_NO={"=sum(T_F1_AMOUNT_CLAIM_PAID)>=$(VLclaim)"},T_D0_CONSULTTING_YEAR={$(=VreportYY)}>}T_F1_AMOUNT_CLAIM_PAID)

,
(sum({$<T_ID_CLAIM_NO={"=sum(T_F1_AMOUNT_CLAIM_PAID)>=$(VLclaim)"},T_D0_CONSULTTING_YEAR={$(=VreportYY)}>}T_F1_AMOUNT_CLAIM_PAID)-
sum({$<T_ID_CLAIM_NO={"=sum(T_F1_AMOUNT_CLAIM_PAID)>=$(VLclaim)"},T_D0_CONSULTTING_YEAR={$(=VpriorYY)}>}T_F1_AMOUNT_CLAIM_PAID))/
sum({$<T_ID_CLAIM_NO={"=sum(T_F1_AMOUNT_CLAIM_PAID)>=$(VLclaim)"},T_D0_CONSULTTING_YEAR={$(=VpriorYY)}>}T_F1_AMOUNT_CLAIM_PAID)
)

The result for report year is true but the result for prior year is wrong ,I don't know something  wrong 

Please help.

[jaibau1993]

Hi!

I can not help you looking into an expression of completely unknown data. Please, share a sample so I can test myself!

On the other hand, if the original issue is solved please mark the answer as solution.

Regards,

Jaime.

[narumonK]

Hi 

I shared a sample data in attachments 

As example data
in Overview pivot have Large Claim
and High claimant
in year 2019 (report Year) we have Large Claim = 777,594 it is true because 777,594 > 700,000(Large Claim)
but High Claimant it is wrong because in 2019 we have 777,594 and in 2018(prior year) we have 1,633,221 but in High Claimant Overview pivot we have 11,483 it is wrong must show 1,633,221 on displays.

Please help.

[jaibau1993 · Accepted solution]

Hi!

I checked your "High Claimant" expression. For prior year you used

sum({$<T_ID_CERT_MEMBER={"=sum(T_F1_AMOUNT_CLAIM_PAID)>=$(VLclaim)"},T_D0_CONSULTTING_YEAR={$(=VpriorYY)}>}T_F1_AMOUNT_CLAIM_PAID)

 where you only sum for those "T_ID_CERT_MEMBER" which sum of "T_F1_AMOUNT_CLAIM_PAID" is greater than VLclaim with current selections. I think that the following expression works:

sum({$<T_ID_CERT_MEMBER={"=sum({<T_D0_CONSULTTING_YEAR={$(=VpriorYY)}>}T_F1_AMOUNT_CLAIM_PAID)>=$(VLclaim)"},T_D0_CONSULTTING_YEAR={$(=VpriorYY)}>}T_F1_AMOUNT_CLAIM_PAID)

Regards,

Jaime.