1 Reply Latest reply: Jul 17, 2017 7:29 AM by Sunny Talwar RSS

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

    Kerim Koc

      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!