Can't pick max of aggr(rangesum(...))

[spcon2016]

Hi colleagues,

I have been trying to pick just one value within an aggregated rangesum for 1 week.

I can show all my A-Customers in a separate table. But if I try to show the number of my A-Customers in a text box, qlikview is going to be my worst enemy.

So, that's the deal:

Here is the appropriate code:

=

IF(

Rangesum(Above(SUM({1}if(YearStart(date(today())) <= BILLING_DATE AND BILLING_DATE <= date(today()), SALES)),0,RowNo(Total)),CUSTOMER_NAME)

<

SUM({1}TOTAL if(YearStart(date(today())) <= BILLING_DATE AND BILLING_DATE <= date(today()), SALES*0.6))

,Rangecount(Above(Count({1}if(YearStart(date(today())) <= BILLING_DATE AND BILLING_DATE <= date(today()) ,CUSTOMER_NAME)),1,RowNo()),CUSTOMER_NAME))

Well, what I do more want as a cocktail in my hand while I'm lying on the Miami beach is to get the last value of this simple looking list. That means, I need the number 19, which is the total number of my A-Customers and depict it in a text box!

And that is what I have been achieved so far:

=

max(aggr(IF(

Rangesum(Above(SUM({1}if(YearStart(date(today())) <= BILLING_DATE AND BILLING_DATE <= date(today()), SALES)),0,RowNo(Total)),CUSTOMER_NAME)

<

SUM({1}TOTAL if(YearStart(date(today())) <= BILLING_DATE AND BILLING_DATE <= date(today()), SALES*0.6))

,Rangecount(Above(Count({1}if(YearStart(date(today())) <= BILLING_DATE AND BILLING_DATE <= date(today()), CUSTOMER_NAME)),1,RowNo()),CUSTOMER_NAME)),CUSTOMER_NAME))

It appears this:

Thank you all in advance for your support!

[sunny_talwar]

Do you have QV12.1 or higher? If you do, then you may be able to get this done very easily. You need a expression based sortable Aggr() function to get this done... check here

Recipe for a Pareto Analysis – Revisited