Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
spcon2016
Partner
Partner

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

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:

pseudoCustomers.JPG

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:

yar.JPG

Thank you all in advance for your support!

1 Reply
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