topic Re: Re: Linear interpolation in QlikView

Linear interpolation

Mon, 13 Jan 2014 12:14:45 GMT

Hi,

I need your help to fix this expression that i am using to get the value of a known point x :

Here is the expression :

Sum({$<[Due Year] ={">=$(=(max([Due Year])-($(V_YearBack)))) <=$(=(max([Due Year]) ))"},

Elapse ={"=$(=(Min({1} Aggr(Min({$<[Due Year] ={">=$(=(max([Due Year])-($(V_YearBack)))) <=$(=(max([Due Year]) ))"},

Elapse ={">=$(=(max(Elapse)))"}>} Elapse),[Due Month],[Due Year]))))"}>}Sales_$(V_Currency))/1000

This expression shows an error but i cannot see where is it ?

My known point x i get it with this expession :

=Min({1} Aggr(Min({$<[Due Year] ={">=$(=(max([Due Year])-($(V_YearBack)))) <=$(=(max([Due Year]) ))"},

Elapse ={">=$(=(max(Elapse)))"}>} Elapse),[Due Month],[Due Year]))

Can you please help to fix it ?

Thanks

Re: Linear interpolation

jolivares — Mon, 13 Jan 2014 12:50:59 GMT

It seems correct. I recomend to you to try to separate the expression and make one at a time or post a qvw to see what is happening.

Re: Re: Linear interpolation

Mon, 13 Jan 2014 15:09:12 GMT

Hi,

Please find attached my qvw application.

I ll give examples:

For year=2012, month=2 and Elapse = -5

i have to see :

year Month Elapse Sales

2011 2 -5 78000/1000

2010 2 -2 67000/1000

For year=2013, month=3 and Elapse = -1

i have to see :

year Month Elapse Sales

2012 3 7 65000/1000

2011 3 25 65000/1000

2010 3 6 76000/1000

This will help me to find the first value for my linear interpolation expression.

Thanks for your help.

Re: Re: Re: Linear interpolation

jerem1234 — Mon, 13 Jan 2014 20:43:37 GMT

What you are trying to do can't be done with set analysis using sum. Set analysis does not evaluate on a row by row basis. In other words, your Elapse value you want the sum for will change depending on the row. Therefore you'll have to use other formulas. Using firstsortedvalue and some set analysis accomplished that. I used the formula:

=firstsortedValue({<[Due Year] ={">=$(=(max([Due Year])-$(V_YearBack))) <$(=(max([Due Year]) ))"}, Elapse= {">=$(=max(Elapse))"}>}Sales_EUR, Elapse)/1000

Please find attached.

Hope this helps!

Re: Linear interpolation

Wed, 15 Jan 2014 11:09:01 GMT

Hi Jerem,

Thanks for your answer ... i tested your solution but it seems like it doesn't resolve my problem.

I can now get the value of the X but still have issue to define the value of the Y:

=Min({1} Aggr(Min({$<[Due Year] ={">=$(=(max([Due Year])-($(V_YearBack)))) <=$(=(max([Due Year]) ))"},

Elapse ={">=$(=(max(Elapse)))"}>} Elapse),[Due Month],[Due Year]))

Is there any other suggestion please ?

Thanks

Re: Linear interpolation

jerem1234 — Wed, 15 Jan 2014 14:04:48 GMT

Not sure what you are looking for then. I used your example tables above to guide what values I expected. If the qvw does not accomplish what you're looking for, can you explain a little more and provide more insight as to the values you are looking for?

Re: Linear interpolation

Thu, 16 Jan 2014 10:36:51 GMT

Hi Jerem,

In the example that you sent this function works fine ... but when i trued to use it in my dev application it doens't return me the expected result (i am using more filters in this application and {1} doens't resolve the problem).

Then, this function work fine in one way (Xa) but when i try to use it to define the values of my Yb (and i have my Xb), this dosn't work at all (empty).

Have you any idea about this problem ?

Thanks

Re: Linear interpolation

jerem1234 — Thu, 16 Jan 2014 14:02:39 GMT

I couldn't say why it won't work in your application. Could be a number of things like a different data model, selections, etc. Also i am not sure what you mean when you are saying Yb and Xb. Do you mean another equation or are you trying to use a different field for the value of Xb other than Elapse? If you could post an example qvw that mimicks what your dev application is, just with scrambled/mock data, then I could help you further.

Also you can try debugging yourself. If the equation I provided doesn't help you, it might just need some extra tweaks for it to work in your dev application depending on your data model/selections. Experimenting with different variations might help you get to where you need to go.

Re: Linear interpolation

Thu, 16 Jan 2014 17:56:25 GMT

Hi Juan,

It shows null values ... and the qvw was posted in my previous message.

Thanks for your help

Re: Linear interpolation

Thu, 16 Jan 2014 17:56:39 GMT

Hi Jerem,

Ok ... i have to define the value of sales in a selected date (X) in my graphic. For that i need to use the linear interpolation formula which consists in defining the value of sales before and after the selected date so:

My date is X and my Sales is Y .... The Xa is the previous date (i have the expression to define it) the Xb is the next date (i defined it too). And the Linear interpolation expresion is : (((Yb-Ya)/(Xb-Xa) ) * (X-Xa)) + Ya = Y (Volume of sales in that date).

Re: Linear interpolation

Tue, 31 May 2016 14:19:30 GMT

Hi imen,

Can u please hepl me on interpolation formula

Plesae explain your expr, with all values in that, i mean used column names variables. I am new to this one.

Thanks