topic Re: RangeSum Aggr - How to use new row count in QlikView

RangeSum Aggr - How to use new row count

Mon, 21 Jan 2013 09:21:47 GMT

Hi Guys,

I am having an issue trying to get this function to work

aggr(

rangesum(

above(

aggr(

sum(Cost),

CalendarMonthAndYear,APR),

0,Rank(Total aggr(sum(Cost),CalendarMonthAndYear,APR))

)

APR)

Essentially what I want it to do is do a sum accumulation based on the subsequent row. The expression

Rank(Total aggr(sum(Cost),CalendarMonthAndYear,APR)) is used instead of RowNo() because I am sorting my previous expression by largest to smallest. and want to rangesum by this order, not the dimension order which RowNo() uses. I am sure this is something quite simple and stupid of me not to figure out, but if you could please help me understand where I went wrong, that would be great.

Thanking everyone in advance for their assistance

Kind Regards,

Byron

Re: RangeSum Aggr - How to use new row count

swuehl — Mon, 21 Jan 2013 10:30:07 GMT

Byron,

it's quite hard to help you here, without knowing your data and the context you are using your expression in.

Could you post a small sample qvw file?

From what I see, I would assume that the above function will be evaluated in the context of your outermost aggr() function, with only dimension APR, and I assume this is not what you want. Please note that dimensions in a aggr() function will be always sorted by load order, so APR might also be sorted different from your expectation.

But again, it's quite hard to help you, please add some details about your setting. (best using a small qvw and a description of your expected outcome).

Regards,

Stefan

Re: RangeSum Aggr - How to use new row count

Mon, 21 Jan 2013 12:53:39 GMT

Hi Stefan,

Thank you for your reply. I'm in a meeting (yes and replying to qlikview messages) but will respond with a sample as soon as possible.

On a side note, really glad to have you looking at my problem. Don't know how you manage to respond to as many people as you Thanks for being one of those guys that makes this community as awesome as it is.

Chat soon

Byron

Re: RangeSum Aggr - How to use new row count

Mon, 21 Jan 2013 13:01:25 GMT

hi,

hope this small expression helps you.

rangesum(above(sum(Value), 0 , RowNo()))

Re: RangeSum Aggr - How to use new row count

Tue, 22 Jan 2013 07:21:18 GMT

Hi Guys,

Attached is a simple example of what I want to achieve. I want to create an expression in a straight table that will give me the same value as AccValue. The rangesum is based on the rank row number. Stefan you mentioned that even when using aggr that the sort order is by default the load order. Is there anyway I can achieve the same result as the inline statement attached using aggr.

Thanks,

Byron

Re: RangeSum Aggr - How to use new row count

swuehl — Tue, 22 Jan 2013 10:44:13 GMT

Byron,

I am not sure if I got your complete requirements:

I would start with a simple straight table, dimension Type and as expression:

=rangesum(above(sum(Value),0,rowno()))

This should produce your requested outcome, as long as the dimension is sorted by sum(Value), i.e. after rank.

You can also use an advanced aggregation quite similar to the one you have posted, thus also show the values with non- sorted dimension:

=aggr(

rangesum(

above(

aggr(

sum(Value),

Type),

0,Rank(Total aggr(-sum(Value),Type),4,0)

)

,Type)

But this will work only if your Type values load order is the same as rank order of their Value. This is the case in your sample, but I assume that'
s not the standard for your application. But if this is the case, you can just use

aggr(

rangesum(

above(

sum(Value),

rowno()

)

,Type)

So you just need an outer advanced aggregation to decouple the results from table dimension sorting.

Regards,

Stefan

Re: RangeSum Aggr - How to use new row count

Tue, 22 Jan 2013 13:25:05 GMT

Hi Stefan,

Just a curtious update to let you know I have seen your reply. I will need to give it some thought tonight when I'm working again. Hopefully something in what you mentioned helps me achieve what I want to do.

I'm also using a personal edition now so I am unable to open your sample.

Thanks for taking the time to write such a detailed explaination, will let you know if I succeeded or not later this evening

Cheers,

Byron