Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

narumonK
New Contributor II

calculate expression from another expression

hi everyone

I want to calculate  expression  from expression  as below

expression.png

I want to calculate var(%) and position var(%) is side by side 2018 as belowexpression2.PNG

 

please help!!

Thank You.

2 Solutions

Accepted Solutions
Partner
Partner

Re: calculate expression from another expression

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:

solve.png

Hope it helps! Regards,

Jaime.

View solution in original post

Partner
Partner

Re: calculate expression from another expression

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.

View solution in original post

10 Replies
Partner
Partner

Re: calculate expression from another expression

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

Re: calculate expression from another expression

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

Re: calculate expression from another expression

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:

solve.png

Hope it helps! Regards,

Jaime.

View solution in original post

narumonK
New Contributor II

Re: calculate expression from another expression

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

year.PNG

year2.PNG

please recommend 

Partner
Partner

Re: calculate expression from another expression

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

Re: calculate expression from another expression

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 

values.PNG

Please help.

Partner
Partner

Re: calculate expression from another expression

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

Re: calculate expression from another expression

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.

Partner
Partner

Re: calculate expression from another expression

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.

View solution in original post