Get the total of an expression

[#1]

Hi all,

I have an expression in a bar chart defined with the above function.

It is something like this : expression for column 1 => if(Week = 1, 1, above(column(1))+1)

(the expression is more complex of course)

I want to be able the read the result of the last line.

Don't really care if I catch it in a separated textbox, in the title of the chart etc...

But as you can imagine, the above() function is giving me a hard time !

I would appreciate any suggestion !

Thanks

[jagan]

Hi,

Can you attach sample file.

Regards,

Jagan.

[#3]

here you go

[jagan]

Hi,

Try this in script

LET v_alpha = 0.1;

LET v_avg = 14;

Data:

LOAD

*,

if(Week=1,$(v_avg), $(v_alpha)* Previous(sales) +  (1-$(v_alpha))*Peek('Temp')) AS Temp;

LOAD *

INLINE [

    Week, sales

    1, 13

    2, 14

    3, 08

    4, 17

    5, 30

    6, 10

    7

];

and use the following expression in text object

=Only({<Week={7}>} Temp)

Regards,

Jagan.

[#5]

Hi,

Thank you very much for your time

Unfortunately, it is not possible for me to do this in the script : v_avg and v_alpha are parameters which can be modified by the user, and the result should be dynamic.

[jerem1234 · Accepted solution]

Here is an example of it outside the chart, the expression wound up being:

=pow((1-v_alpha),max(Week)-1)*v_avg + sum(aggr(pow((1-v_alpha),$(=max({1}Week)-1)-Week)*(sum({<Week-={$(=max(Week)-1),$(=max(Week))}>}sales)*v_alpha), Week)) + sum({<Week={$(=max(Week)-1)}>}sales)*v_alpha

Hope this helps!

[#7]

Hi Jerem,

Wow, thank you very much

I did not even think it was possible to transform this recursive formula into a standard formula!

I made some changes into it and it works perfectly

firstly I removed in $(=max(Week)-1) (because i was afraid it might screwd the result if the user make some selections in the weeks

secondly I changed you last part (if week = max(week)-1, then $(=max(Week)-1)-Week) = 0 so it is OK

finaly I used min(Week) instead of 1 (my weeks don't start always at 1).

So now my expression looks like this :

pow( (1-v_alpha), max(Week)-min(Week)) * v_avg +

sum(aggr(pow((1-v_alpha),$(=max(Week)-1)-Week)(sum({<Week-={$(=max(Week))}>}sales)v_alpha), Week))

So thanks for your help !!!

Yannick

[jerem1234]

Glad it helped! The trick was re-writing the recursive formula into a polynomial (as you can see with the pow's). Then each week had an associated term for it (which the aggr takes care of). Just a little alegbra