Solved: Re: RangeSum(Above(RangeSum(Above( Why need repeat... - Qlik Community

paulyeo11 · ‎2016-06-28

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

rubenmarin · ‎2016-08-02

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

View solution in original post

paulyeo11 · ‎2016-06-28

qvf

jonathandienst · ‎2016-07-29

No idea. Have you tried modifying the expression?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

paulyeo11 · ‎2016-07-29

Hi Jona

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

Paul

Sent from my iPhone

rubenmarin · ‎2016-07-29

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 · ‎2016-07-29

Hi Rub

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

Paul

Sent from my iPhone

rubenmarin · ‎2016-07-29

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 · ‎2016-07-29

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 · ‎2016-08-02

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

rubenmarin · ‎2016-08-02

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

)

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