Linear interpolation

[#1]

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

[jolivares]

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.

[#3]

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.

[jerem1234]

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!

[#5]

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

[jerem1234]

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?

[#7]

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

[jerem1234]

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.

[#9]

Hi Juan,

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

Thanks for your help

[#10]

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).