Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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