Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
_Julia_
Contributor II
Contributor II

Rangesum(above()) with aggr by two parameters

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.

_Julia__0-1692968315039.png

 

_Julia__0-1692966433950.png

 

Labels (1)
1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

Oleg_Troyansky_0-1692976539434.png

 

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,  

 

View solution in original post

5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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_
Contributor II
Contributor II
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

Oleg_Troyansky_0-1692976539434.png

 

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_
Contributor II
Contributor II
Author

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

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: