topic Re: How to compute Stock Amount using RangeSum(Above( and get amount =-1,505,155.9 ? in QlikView

How to compute Stock Amount using RangeSum(Above( and get amount =-1,505,155.9 ?

paulyeo11 — Thu, 09 Jun 2016 23:18:28 GMT

Hi All

when i compute using Sum(Amount) it return amount =-1,505,155.9

But when i using below expression :-

ADD RATE FOR GL TABLE

RangeSum(

Above(

RangeSum(

Above(

Sum({<year, month>}[Amount]*-1), 0, RowNo()))),

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

YearMonth)))

Now it return -361,237,416 , How to make it return 1,505,155.9 ?

Paul

Re: How to compute Stock Amount using RangeSum(Above( and get amount =-1,505,155.9 ?

Fri, 10 Jun 2016 02:11:20 GMT

Hi,

I'm not really clear what you're trying to do there, but if you remove TOTAL from this expression

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

YearMonth)))

it'll return -1,505,155.9

Re: How to compute Stock Amount using RangeSum(Above( and get amount =-1,505,155.9 ?

paulyeo11 — Fri, 10 Jun 2016 06:52:53 GMT

hI nHU nGO

Thank you for your sharing , but then previous month amount wrong. Previous month should be 1.4 million, Now return zero.

Paul

Re: How to compute Stock Amount using RangeSum(Above( and get amount =-1,505,155.9 ?

Fri, 10 Jun 2016 06:56:04 GMT

May I ask what are you trying to achieve there? i'm not clear about your requirement yet.

Re: How to compute Stock Amount using RangeSum(Above( and get amount =-1,505,155.9 ?

paulyeo11 — Fri, 10 Jun 2016 08:59:18 GMT

Hi Nhu Ngo

My issue is i need to get the LY Amount using below expression :-

(RangeSum(Above(RangeSum

(Above(

Sum

(

{

year, month, $(ColumnDim801)={$(ColumnDim801)}

}

[Amount]/1000), 0, RowNo()))),

Sum(

{

$(ColumnDim801)={$(ColumnDim801)}>

}

TOTAL Aggr(Sum(

{

year, month, $(ColumnDim801)={$(ColumnDim801)}

}

[Amount]/1000), YearMonth

))))

See my Enclosed QV Doc.

Paul

Re: How to compute Stock Amount using RangeSum(Above( and get amount =-1,505,155.9 ?

Fri, 10 Jun 2016 10:48:23 GMT

Hi Paul,

Sorry it's still not clear to me. So you want to get LY amount by using your expression. But that expression doesn't return value correctly, is that right? Can you explain what that expression means. Is that accumulate stock?

Also, you mentioned if remove TOTAL then Previous month amount is 0? can you send me screenshot?