Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Get the total of an expression

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

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

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!

View solution in original post

7 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach sample file.

Regards,

Jagan.

Not applicable
Author

here you go

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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
Specialist II
Specialist II

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!

Not applicable
Author

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
Specialist II
Specialist II

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