20 Replies Latest reply: Aug 3, 2016 5:21 AM by Ruben Marin

# 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

qvf

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

No idea. Have you tried modifying the expression?

• ###### 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 ?

Paul,

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

-

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

As Ruben Explanation why should use something like this

Here, 2 Acting like two times of Above(Above(Sum(Sales)))

try, and let me know

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

Hi Anil

I have copy and paste your expression into Table 2 , it display null value.  ( Table 1 using orginal expression , it work fine. ) .

My objective is to make the expression simple . So that i can modify the expression. As i have trouble getting the aP LY value = 3,384,545,900

I have no issue in getting YTD value See my Table 3.

Paul

• ###### 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:

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

Hi  Ruben

I have no issue with YTD amount , but for the LY YTD amount , your expression as below it get correct value when i select manual field = aP ( by org button):-

But in my actual app , I need to insert condition to above expression :-

aP = {'aP'}

So the expression should be :-

Can you share with me where I go wrong ?

Paul

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

Hi Ruben

Many thank , now it work fine , i using the below expression :-

Paul

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

Nice, I worked with the sample V70 and the CY expression didn't have that condition.

In case aR has more possible values and you want to override user selections to fix 'aR' as the value selected I think you'll need to set the condition in the Sum() enclosing the Aggr():

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

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

Hi Ruben

Thank you very much , i just test the expression , it work fine now.

Where to insert aR = {'aR'} into Average aR expression ?

About 6 hour ago Sunny help me with the above post , i need to get the Average amount of aR ( Account Receivable ) , Hope you can also advise me if you are free.

Paul

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

Hi Paul, your last post in that thread says it's working, is there something to advise? If it is, can you tell me wich sample I can pick to make some test and what's the expected result?

I'm not free but I have intervals when the computers works, instead of just waiting I log to the community and try to give an answer or two before the process ends.

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

Hi Jona

Thank you for trigger my post on 29 July , after i posted one month with out any reply. immediately 2 person answer to my post , and now i got the solution , i am able to move on.

Paul

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

• ###### 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?

• ###### 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 .

Paul

Sent from my iPhone

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

)

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

Paul, Why are using duplicates for expression

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

)

Try this, Can i know what are the Year & Months as well?

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

Hi Anil & Ruben

Year & year or Month & month both field are same. ( When i started learn Qlik View , I create this in master cal , and some time i use Year , and some time i use year. For SET expression all i use year ) , so my panel filter is wrong , should have change to year instead of Year.

Thank you for your expression , yesterday i notice some thing wrong with the expression. ( I refer to above expression when i first post the quesrtion ) it does able to compute the month amount , but it unable to compute LY same period amount ). So now it is very messy. i need to stop doing this for a while. and most likely relook again later stage , so that it not discounrage me from playing QV.

Paul