RangSum in Text Object

[mhc4qlik]

Hello Communitiy,

I have an RangeSum Issue in Qlikview. that I cannot solve on my own. I realy hope you can help me.

Is it possible to sort a field in the rangeSum function?. It should start to sum with the largest value.

I tried to build a small example. I have generated three fields with an Inline Table. Supplier, Spend and Complete Spend.

At the end there should be only one Number in a text object: The Amount of Suppliers, who has 80% of the Total Spend.

It is important to start with the largest number.

The Number in the Table is right. 5 is correct, but I cannot get the right number in the text object.:

count(
aggr(
if(
(rangesum(Above(Total sum(SPEND),0,RowNo(Total)))
/
COMPLETE_SPEND) <= 0.8,
count(SPEND))
, SPEND)
)

Thank you in advance!

[sunny_talwar]

See if this discussion helps you towards a way to do this:

ABC Analysis in Qlikview

[mhc4qlik]

Hello Sunny,

thank you for your answer. Unfortunately this discussion doesn't help. My problem is it to get the number in a text object. In the table it works perfectly.

[sunny_talwar]

The problem is that RangeSum is dimension dependent and since your text box doesn't have a dimension it doesn't really work. The work around is to use Aggr() function to give it a dimension, but now the issue is that Aggr() is sorted in a particular order (load order before QV12 and starting QV12 you can sort ascending or descending by your field names). The important thing to note is that it can still not sort by expression in QV12.

In your application you have it sorted by SPEND which is field. If that is the case in your actual application and you have QV12, this may still work. But if you have to sort by Sum(Spend) descending, then the only option is the method that Oleg mentioned in his post here: So How Many Customers Make Up Most of Your Sales?

[Frank_Hartmann]

Hi Martina,

have a look at the attached qvw.

hope this helps

[sunny_talwar]

Did you attach the right qvw?

[Frank_Hartmann]

Yes.

Now it should be the correct one 🙂

[sunny_talwar]

But this value is static and will not change based on selection. For instance if we have Supplier by country and the use want to see this number change based on selection of a particular country, that would not happen here because it all done in the script, right?

[Frank_Hartmann]

Yes, you are absolutly right

I missread the requirement.

[sunny_talwar]

franky_h79‌ I am not saying you are incorrect and I don't even know if changes based on selection is a requirement or not. But just trying to point out that this could be an issue