topic Re: RangeSum(Above(RangeSum(Above( Why need repeat twice ? in QlikView

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

paulyeo11 — Wed, 29 Jun 2016 03:49:44 GMT

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

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

paulyeo11 — Wed, 29 Jun 2016 03:52:09 GMT

qvf

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

jonathandienst — Fri, 29 Jul 2016 10:02:30 GMT

No idea. Have you tried modifying the expression?

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

paulyeo11 — Fri, 29 Jul 2016 10:21:19 GMT

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 ?

rubenmarin — Fri, 29 Jul 2016 10:27:46 GMT

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 ?

paulyeo11 — Fri, 29 Jul 2016 10:38:43 GMT

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 ?

rubenmarin — Fri, 29 Jul 2016 10:58:06 GMT

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 ?

paulyeo11 — Fri, 29 Jul 2016 11:29:08 GMT

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

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

paulyeo11 — Tue, 02 Aug 2016 07:17:06 GMT

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

rubenmarin — Tue, 02 Aug 2016 07:35:56 GMT

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 ?

Anil_Babu_Samineni — Tue, 02 Aug 2016 07:47:13 GMT

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 ?

paulyeo11 — Tue, 02 Aug 2016 08:12:06 GMT

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

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

Anil_Babu_Samineni — Tue, 02 Aug 2016 08:51:42 GMT

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 ?

paulyeo11 — Tue, 02 Aug 2016 10:12:01 GMT

Hi Anil

Thank you for your sharing.

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 ?

rubenmarin — Tue, 02 Aug 2016 12:39:12 GMT

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

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

paulyeo11 — Tue, 02 Aug 2016 18:46:01 GMT

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

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

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

aP = {'aP'}

So the expression should be :-

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

Can you share with me where I go wrong ?

Paul

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

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

paulyeo11 — Wed, 03 Aug 2016 00:21:03 GMT

Hi Ruben

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

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

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

Paul

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

paulyeo11 — Wed, 03 Aug 2016 00:25:00 GMT

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 ?

rubenmarin — Wed, 03 Aug 2016 06:19:29 GMT

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 ?

paulyeo11 — Wed, 03 Aug 2016 06:43:30 GMT

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