topic Re: calculate expression from another expression in QlikView

calculate expression from another expression

narumonK — Fri, 07 Jun 2019 06:29:04 GMT

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.

Re: calculate expression from another expression

jaibau1993 — Fri, 07 Jun 2019 06:41:20 GMT

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.

Re: calculate expression from another expression

narumonK — Fri, 07 Jun 2019 06:51:14 GMT

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

Re: calculate expression from another expression

jaibau1993 — Fri, 07 Jun 2019 07:09:38 GMT

Sorry, I did not understand your issue.

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

Create an island table with as many "Codes" as dimensions you'll need. In the example there are two codes: year and variation
Use a calculated dimension like =Pick(Code,Year,'Variation')
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.

Re: calculate expression from another expression

narumonK — Fri, 07 Jun 2019 07:26:22 GMT

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

Re: calculate expression from another expression

jaibau1993 — Fri, 07 Jun 2019 07:36:04 GMT

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.

Re: calculate expression from another expression

narumonK — Fri, 07 Jun 2019 08:39:02 GMT

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.

Re: calculate expression from another expression

jaibau1993 — Sun, 09 Jun 2019 16:05:22 GMT

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.

Re: calculate expression from another expression

narumonK — Mon, 10 Jun 2019 03:38:06 GMT

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.

Re: calculate expression from another expression

jaibau1993 — Mon, 10 Jun 2019 06:28:11 GMT

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.

Re: calculate expression from another expression

narumonK — Mon, 10 Jun 2019 06:47:51 GMT

excellent!!!
many thank