topic Re: RangSum in Text Object in QlikView

RangSum in Text Object

mhc4qlik — Tue, 22 Nov 2016 12:56:15 GMT

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!

Re: RangSum in Text Object

sunny_talwar — Tue, 22 Nov 2016 13:00:31 GMT

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

ABC Analysis in Qlikview

Re: RangSum in Text Object

mhc4qlik — Tue, 22 Nov 2016 13:51:48 GMT

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.

Re: RangSum in Text Object

sunny_talwar — Tue, 22 Nov 2016 14:05:13 GMT

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?

Re: RangSum in Text Object

Frank_Hartmann — Tue, 22 Nov 2016 19:49:55 GMT

Hi Martina,

have a look at the attached qvw.

hope this helps

Re: RangSum in Text Object

sunny_talwar — Tue, 22 Nov 2016 19:53:57 GMT

Did you attach the right qvw?

Re: RangSum in Text Object

Frank_Hartmann — Tue, 22 Nov 2016 19:56:00 GMT

Yes.

Now it should be the correct one 🙂

Re: RangSum in Text Object

sunny_talwar — Tue, 22 Nov 2016 19:57:44 GMT

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?

Re: RangSum in Text Object

Frank_Hartmann — Tue, 22 Nov 2016 20:00:13 GMT

Yes, you are absolutly right

I missread the requirement.

Re: RangSum in Text Object

sunny_talwar — Tue, 22 Nov 2016 20:02:34 GMT

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

Re: RangSum in Text Object

mhc4qlik — Wed, 23 Nov 2016 08:08:10 GMT

Thank you for your help!

But Sunny is right. The number should change based on selections. I solved it now by generating a sorted table in the script . By storing it in a qvw and loading it again the numbers are sorted and my function in the text object works!

Re: RangSum in Text Object

kavita25 — Tue, 06 Dec 2016 13:46:39 GMT

Can u please share the app..

I have the same requirement

Re: RangSum in Text Object

mhc4qlik — Wed, 07 Dec 2016 08:44:12 GMT

Hello Kavita,

of course I can share the app.

There are two qvws. In the first step I sort the table and store it in a qvd. Then I can use the function in the text object in the second qvw.

I hope it helps.