topic Re: RangeSum() and Above() in QlikView

RangeSum() and Above()

Thu, 21 May 2015 14:57:13 GMT

Hey fellas.

I've found a solution to an issue I have which works (almost) perfectly however I'm struggling to understand how it works.

The original solution I found here and was solved by swuehl...

Accumulation Line Chart can not stop to current month

Essentially it's this expression...

if(

Sum ({<financial_year={'$(=selected_year)'} >} budget),

rangesum(Sum ({<financial_year={'$(=selected_year)'} >} budget),

above("Fred")

))

My main issue (aside from not really understanding what any of it is doing) is the "Fred". I understand that this refers to itself i.e. its label, however I don't want a static label. The label is '=selected_year' without the quotes.

So what do i need to put in place of "Fred" so that the expression works and the label changes with the year selected?

Thanks,

Foldy.

Re: RangeSum() and Above()

sunny_talwar — Thu, 21 May 2015 15:01:54 GMT

You can try using Column() function in place of a your dynamic label.

Re: RangeSum() and Above()

sunny_talwar — Thu, 21 May 2015 15:05:16 GMT

Or I might say 'Fred'. Try

if(

Sum ({<financial_year={'$(=selected_year)'} >} budget),

rangesum(Sum ({<financial_year={'$(=selected_year)'} >} budget),

above([Column(1)]) //Using 1 assuming its the first expression in your chart

))

Re: RangeSum() and Above()

cesaraccardi — Thu, 21 May 2015 15:05:50 GMT

Hi Foldy,

I think you might be able to replace

above("Fred")

above(Column(X))

Where X is the position of the expression: Expression 1 = 1, Expression 2 = 2, etc... The only issue I see with this is that if you are using conditionally enabled expressions it might not work properly.

Cesar

Re: RangeSum() and Above()

rubenmarin — Thu, 21 May 2015 15:07:40 GMT

Hi Foldy, if you only have one expression maybe it works using "Column(1)" instead of column name:

if(

Sum ({<financial_year={'$(=selected_year)'} >} budget),

rangesum(Sum ({<financial_year={'$(=selected_year)'} >} budget),

above(Column(1))

))

This is doing: If there is a budget for this ¿month? (the month in dimension), add this budget value to the column value in the previous row.

Maybe this option also works:

if(

Sum ({<financial_year={'$(=selected_year)'} >} budget),

rangesum(Sum ({<financial_year={'$(=selected_year)'} >} budget),

above("$(=selected_year)")

))

Re: RangeSum() and Above()

Thu, 21 May 2015 15:13:55 GMT

Thanks chaps. Works a treat. Sunindia got in there first but everyone has helpful answers.

Re: RangeSum() and Above()

Thu, 21 May 2015 15:14:46 GMT

Thanks for the extra explanation Ruben. You've made it clearer for me.