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

[paulyeo11]

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

[Anil_Babu_Samineni]

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?

[paulyeo11]

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

[Anil_Babu_Samineni]

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

[paulyeo11]

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

[rubenmarin · Accepted solution]

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

[paulyeo11]

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

[paulyeo11]

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

[paulyeo11]

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

[rubenmarin]

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

[paulyeo11]

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