4 Replies Latest reply: Jun 27, 2012 9:05 AM by Richard Lucas RSS

    Advanced Aggr function help

    Richard Lucas

      Hi, I have a dataset at the moment which I create in Qlik, then extract to Excel for further calculation. I am sure it can all be done in Qlik, but need assistance to work out how.

      PROBLEM DESCRIPTION:

       

      I have a Base table with the following fields:

       

      CUSTOMERPRODUCTYearMonthSALES_REVENUE

       

       

      I Use a straight Table (dimensions CUSTOMER & PRODUCT) to show which Customers bought in different years:

       

      CUSTOMERPRODUCTSales Revenue 20082008FlagSales Revenue 20092009Flag
      CUSTOMERPRODUCTSUM({<Year={2008}>}SALES_REVENUE)IF(SUM({<Year={2008}>}SALES_REVENUE)=0,0,1)

      SUM({<Year={2009>}SALES_REVENUE)

      IF(SUM({<Year={2009}>}SALES_REVENUE)=0,0,1)
      A110012001
      A250100
      B1001001
      B20000
      B3751751

       

      Then I take the data to Excel and filter the flag columns to show, per product, how much Revenue is from Lost, New or continuing customers ("Customer Base") - without showing the customer as a dimension

      (eg Customer A bought Product 2 for $50 in 2008, but $0 in 2009. They are lost customer. Their Revenue from 2008 should show in the "Lost Customers" field below for Product 2.:

       

      Sales Revenue
      Lost CustomersCustomer BaseNew Customers
      PRODUCT

      IF(2008Flag = 1 AND 2009Flag=0),

      SUM(Sales Revenue 2008),0)

      IF(2008Flag = 1 AND 2009Flag=1),

      SUM(Sales Revenue 2009),0)

      IF(2008Flag = 0 AND 2009Flag=1),

      SUM(Sales Revenue 2009),0)

      10200100
      25000
      30750

       

       

      I do the same with a Count of customers to work out how many customers are "Lost", "New" or "Base".

       

      I want to do all of this as one step in a Straight Table.

      If I keep CUSTOMER & PRODUCT as dimensions I can get the data to show correctly.

       

      E.g. Lost Customer Revenue code: 

      IF
      (SUM({<Year={2008}>}SALES_REVENUE)>0 AND SUM({<Year={2009}>}SALES_REVENUE)<=0,
       SUM({<Year={2008}>}SALES_REVENUE),0) 
      

       

      But if I remove the CUSTOMER field as a dimension the calculation is a sum of all revenue for 2008 vs 2009, not "per customer".

      Therefore I need an "Aggr" function.

       

      I have tried:

      IF 
      (Aggr(SUM({<Year={2008}>}SALES_REVENUE),CUSTOMER)>0 AND Aggr(SUM({<Year={2009}>}SALES_REVENUE),CUSTOMER)<=0,
       Aggr(SUM({<Year={2008}>}SALES_REVENUE),CUSTOMER),0)
      

       

      Also tried:

      IF
       (Aggr(SUM(Total {<Year={2008}>}SALES_REVENUE),CUSTOMER,Year)>0 AND Aggr(SUM(Total {<Year={2009}>}SALES_REVENUE),CUSTOMER,Year)<=0,
      Aggr(SUM(Total {<Year={2008}>}SALES_REVENUE),CUSTOMER,Year),0)
       

       

      I think I need an "Aggr" function around the whole IF statement.

      Does anyone have any ideas?