Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Rolling of customer data one year go

  1. #1
    Join Date
    Mar 2014
    Posts
    181

    Default Rolling of customer data one year go

    Hello members,

    I have a requirement where I am

    I am supposed to generate a report out of the postgres orders table and this report involves rolling of customers 1 year or 6 months ago.

    Below is the schema:

    Code:
    create table ord    (
          cust_id VARCHAR(30),
          ord_id VARCHAR(30),
          item_qty int,
          item_iml_ship_qty int,
          item_extended_cost_amt numeric(18,2),
          item_extended_actual_price_amt numeric(18,2),
          ord_submitted_date date
          );
        INSERT INTO ord(cust_id, ord_id, item_qty,item_iml_ship_qty, item_extended_cost_amt, item_extended_actual_price_amt, ord_submitted_date)
        SELECT 'abcd1234', 'ord12034', 1, 1, 40, 100, '2011-01-01'::DATE
        UNION
        SELECT 'abcd1234', 'ord123457', 4, 4, 50, 100, '2009-10-12'::DATE
        UNION 
        SELECT 'abcd1235', 'ord123458', 1, 1, 50, 120, '2010-10-01'::DATE
        UNION
        SELECT 'abcd1235', 'ord123459', 4, 4, 50, 100, '2010-12-31'::DATE
        UNION
        SELECT 'abcd1235', 'ord123467', 5, 5, 20, 130, '2012-01-01'::DATE
        UNION
        SELECT 'abcd1239', 'ord123487', 4, 4, 50, 100, '2013-07-01'::DATE
        UNION
        SELECT 'abcd1239', 'ord123454', 3, 3, 50, 80, '2014-01-01'::DATE
        UNION
        SELECT 'abcd1239', 'ord123456', 2, 2, 30, 60, '2014-06-01'::DATE
        UNION
        SELECT 'abcd1234', 'ord123456', 1, 1, 50, 100, '2014-08-01'::DATE; `
    
    
    This is the query that I have generated
    
    
       
    
    
          WITH ord_cte as
    (
    SELECT EXTRACT(YEAR FROM o.ord_submitted_date)ord_yy , o.cust_id,
                o.ord_id,
              
                count(DISTINCT o.cust_id) AS unique_pkey_customer_count,
             
                COALESCE(sum(
                    CASE
                        WHEN COALESCE(o.item_qty, 0) >= COALESCE(o.item_iml_ship_qty, 0) THEN COALESCE(o.item_qty, 0)
                        ELSE COALESCE(o.item_iml_ship_qty, 0)
                    END), 0::bigint) AS "Unit_Sales_Count",
                COALESCE(sum(o.item_extended_cost_amt), 0::numeric) AS "Lifetime COS",
                COALESCE(sum(o.item_extended_actual_price_amt), 0::numeric)::numeric(18,2) AS "Gross_Revenue_Amt",
              
                COALESCE(sum(o.item_extended_actual_price_amt) - sum(o.item_extended_cost_amt), 0::numeric)::numeric(18,2) AS "Gross_Profit_Amt",
                count(DISTINCT o.ord_id) AS "Total_LifeTime_No_Of_Orders",
            
                COALESCE(count(DISTINCT o.ord_id)::double precision / count(DISTINCT o.cust_id)::double precision, 0::double precision) AS "Lifetime_Ord_Per_Cust"
                
               FROM ord o LEFT JOIN ord d ON d.ord_submitted_date Between O.ord_submitted_date - INTERVAL '365 days' and o.ord_submitted_date 
               group by o.ord_submitted_date, o.cust_id,
                o.ord_id
      )

    My code might look complicated but I am required to put this in kettle which wouldn't be a problem but I need some guidance on how to do this on how to calculate the average one year spend and avg 6 months spend of customer.


    Please I need your help.

    Thanks,

    Ron

  2. #2
    njain111 Guest

    Default

    In a transformation use:
    'table input step' to get last years transactional line items --> Use 'Group By Step' to group by 'UserId' and in Aggregate section use Type=Avg and Subject=Customer Spending.

  3. #3
    Join Date
    Mar 2014
    Posts
    181

    Default

    Quote Originally Posted by njain111 View Post
    In a transformation use:
    'table input step' to get last years transactional line items --> Use 'Group By Step' to group by 'UserId' and in Aggregate section use Type=Avg and Subject=Customer Spending.
    I am using the running totals approach.

    The approach you gave might not the best; this is just my opinion.

    I believe there is more to just using the input step and the Group by.

    Thanks.

    Ron

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You know... This really screams "Build a cube" to me.
    I'm not 100% sure how you would do "Last 12 months" and "Last 6 months", but there are a bunch of wizards over in the Mondrian forum that could explain it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.