Rangesum(above()) with aggr by two parameters

[_Julia_]

Hi! I am new to this forum, so I would appreciate your help.

I am doing segments assignment for addresses and SKU based on their cumulative profit when sorted by profit desc (A - 0-80% of cumulative profit, B - 80-95% of cumulative profit, C - all other). It all goes well when I work with one element - ony address or only SKU. But when I want to calculate cumulative profit for SKU within address, I do not understand how I can do it. It seems there should be a way, I just can't figure it out.

Here are tables for address and for SKU which I have in Qlik.
And below I attach excel screenshot to better show what I want to achieve. On the right in the red border is the table that I'm trying to recreate in Qlik. I tried different variations of rangesum above & aggr but nothing works.

Any thoughts how I can calculate cumulative profit for this?

Thanks.

[Oleg_Troyansky]

Hi Julia,

Kudos on using such advanced features in your calculations - this could be one of the exercises for my session on  advanced Set Analysis and AGGR() at the Masters Summit for Qlik!

While it's hard to troubleshoot the formula from a screenshot, I'd look in two directions:

1. Technically, the AGGR() function returns an array of values, while a table cell is expecting a single value. Even though in your case, there should be only one number for each combination of Address and SKU, it's possible that using sorted structured arguments in your AGGR() somehow messes up this logic. Try enclosing your AGGR() in a sum() function and see if that makes a difference.

2. I think you have too many parentheses around your structured parameters, and I know that those matter, specifically around structured parameters. Try to remove any unnecessary parentheses. In a nutshell, the AGGR() function with sorted structured parameters should look like this:

AGGR(

<aggregation formula>,

(Dim1, (sort, order)),

(Dim2 (sort, Order))

)

In your formula, you have an extra set of parentheses around the two dimensions. In my testing, it "broke" the formula. Try removing them and see if it works.

If you posted a sample app, I wouldn't mind to look into it for you.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[_Julia_]

Hi Oleg,

thanks for replying. I removed extra parentheses and the formula started working! Although not quite right. It doesn't sort the values in the right way - by address asc and then by profit desc. Not speaking of the "rowno()" component - so it just sums all the rows while they must be summed up within the addresses. 

I attach qvf file with the corrections I wrote about.

[Oleg_Troyansky · Accepted solution]

Julia,

I believe your formula is working as expected. The table is not sorted the same way as the AGGR() data, and that could cause a confusion. If you sort the table the same way, you should see the expected results.

You can also test the RowNo() functionality by adding this Measure:

aggr(rowno(),
(Address, (TEXT, ASCENDING)), ( SKU, (=sum(Profit), DESCENDING )) )

This way, you will be able to see the order of each SKUs by profit, no matter how the table is sorted. When the table is sorted properly, the RowNo() should ascend sequentially and restart from 1 for each Address.

Now, there is one more caveat to keep in mind. When you sort SKUs by sum(Profit) in the AGGR() function,  the sorting order is applied to the "whole" SKU, not to the combination of a particular SKU for a particular Address, as you'd expect. That causes additional issues. To sort SKUs by profit in the context of the particular Address, you'd need to create a combined field with Address and SKU concatenated together:

Address & '|' & SKU

Only then you can get the absolutely correct sorting order of SKU by profit for each Address.

Cheers,  

[_Julia_]

Oleg, thanks a lot, it's amazing!

The concatenated field is definitely what was missing. I replaced [SKU] field with [Address|SKU] field in all formulas. And it finally worked!

So rowno() functionality looks like this:
aggr(rowno(), (Address, (TEXT, DESCENDING)), ([Address|SKU], (=sum(Profit), DESCENDING)))

and the whole formula of cumulative sum is:
aggr(rangesum(above( sum([Profit]), 0, aggr(rowno(), (Address, (TEXT, DESCENDING)), ([Address|SKU], (=sum(Profit), DESCENDING))) )),
(Address, (TEXT, ASCENDING)), ( [Address|SKU], (=sum(Profit), DESCENDING )) )

[jamesmichael323]

It seems like you're referring to an expression in QlikView or Qlik Sense, a business intelligence tool used for data visualization and analysis. The Rangesum(above()) function is often used to calculate a running total in QlikView or Qlik Sense, and you mentioned that you want to use it with aggregation by two parameters.

Here's a general idea of how you might use the Rangesum(above()) function with aggregation by two parameters in QlikView or Qlik Sense: