Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Hi All

Below expression from Sunny :-

RangeSum(Above(RangeSum

(Above(

Sum

(

{

<

year, month, aR={aR}

>

}

[Amount]*1)/Rate, 0, RowNo()))),

-

Sum(

{

<

aR={aR}>

}

TOTAL Aggr(Sum(

{

<

year, month, aR={aR}

>

}

[Amount]*1)/Rate, YearMonth

)))

above expression work fine.

Can some one share with me why it repeat twice on RangeSum(Above :-


RangeSum(Above(RangeSum(Above(



Paul

1 Solution

Accepted Solutions

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Hi Paul, in that sample your table on the right doesn't have a row above so it's aonly doing the this part of the expression:

-Sum(TOTAL Aggr(Sum({<year, month>}[Amount]*-1), YearMonth))

Yo can try it leaving only this as expression, it only sums all the amounts... in a wird thing beacuse it shows the negative of a value multiplied by -1 wich can be simplified to:

Sum(TOTAL Aggr(Sum({<year, month>}[Amount]), YearMonth))


And if you want to work based in the (max) year and month selected:

Sum({$<year, month, date={'<=$(=Date(Max(date)))'}>}TOTAL Aggr(Sum({<year, month, date={'<=$(=Date(Max(date)))'}>}[Amount]), YearMonth))


For the last year you can sum all values until 12 months before the max date:

Sum({$<year, month, date={'<=$(=AddMonths(Max(date), -12))'}>}TOTAL Aggr(Sum({<year, month, date={'<=$(=AddMonths(Max(date), -12))'}>}[Amount]), YearMonth))

20 Replies
paulyeo11
Valued Contributor II

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

qvf

MVP
MVP

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

No idea. Have you tried modifying the expression?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
paulyeo11
Valued Contributor II

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Hi Jona

This expression from sunny , I don't understand how the expression work , so I cannot modify.

Paul

Sent from my iPhone

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Hi Paul, the inside RangeSum() sums the values returned by Above, wich returns a value for each of the rows above.

The outside RangeSum sums the values returned to the inside RangeSum and the result returned by the Sum(Aggr()) (wich has a minus sign, so it really substracts)

paulyeo11
Valued Contributor II

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Hi Rub

Thank you for your explain , not so easy to understand .

Paul

Sent from my iPhone

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Yes, it's hard and I can't test, removing the duplicated RangeSum(Above( it will be:

RangeSum(

   Above(Sum({<year, month, aR={aR}>}[Amount]*1)/Rate, 0, RowNo()),

- Sum({<aR={aR}>} TOTAL Aggr(Sum({<year, month, aR={aR}>}[Amount]*1)/Rate, YearMonth))

)

And I'm not sure if this can work, can you try it?

paulyeo11
Valued Contributor II

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Hi Sir

Great you have try to break the expression into smaller , I will test on Sunday night , as I am going for hiking now .

In fact this is what I need , as I am not able to get the figure I want for 2 month .

Appreciate your help.

Paul

Sent from my iPhone

paulyeo11
Valued Contributor II

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

‌Hi Ruben

i have tested your expression , it does not give any error.

BUt but now I have issue because I am stuck. Don't know how to break the expression into 2.

Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ?

Hi Paul, I don't understand what you meant  with "break the expression into 2"... Make the expression work for 2 months?

I think I will need a sample to try that or we will spend a lot of time with guessings but here goes one guess:

RangeSum(

  Above(Sum({<year, month, aR={aR}>}[Amount]*1)/Rate, 0, RowNo()),

- Sum({<year, month, aR={aR}>} TOTAL Aggr(Sum({<year, month, aR={aR}>}[Amount]*1)/Rate, YearMonth))

)

Community Browser