1 Reply Latest reply on Jul 23, 2015 12:26 AM by gagandeep.singh.3

    How to draw trend and moving average on Created(Order Date), Closed/Resolved(Ship Date) and Open(Order in between order and shipment)

    gagandeep.singh.3

      Hi,

      Today is my first week into Tableau. I have been given task to create worksheets exactly similar to the ones given below.

       

      1) Weekly Totals

      Weekly_totals.png

      2) Daily Totals

      Daily_totals.png

      3) Daily Workloads

      Daily_workload.png

       

      I am using Superstore data and was able to produce "Orders created" and "Orders resolved" using the following post.

      combine two date fields on same axis

       

      Questions

      1) Using the above method, doesn't allow me to create trend lines. How can we create trend line and moving averages?

      2) I am able to get the Created(Order Date) and Resolved(Ship Date) for that particular day but for the days when there is no data, I cant get value 0 to indicate that there was no activity(eg for Sundays). How to get 0? My cross tab join will never have that data?

      3) If I continue with the above solution then, how can we get the value of " Order Open in Queue start of day"?

       

      Will appreciate pointers and I will try to workout the rest....Hopefully!!!!

       

      Thanks,

      Gagan

        • 1. Re: How to draw trend and moving average on Created(Order Date), Closed/Resolved(Ship Date) and Open(Order in between order and shipment)
          gagandeep.singh.3

          An alternative solution could be derived if we can write the SQL in following format. I worked it out on Oracle which is our back end.

           

           

          Select time_id1 time_id, Order_count,SHIP_COUNT,Open_count FROM

           

          (select time_id1, Order_count from (

          select b.time_id time_id1, count(a.ORDER_DATE) Order_count from TEMP_ORDER a, R0000_CALENDAR b

          where (b.TIME_ID  = a.ORDER_DATE )  -- Outer join in Oracle

          and b.time_id between to_date('010111','ddmmyy') and to_date('311211','ddmmyy')

          group by b.TIME_ID) a ) ,

           

          (select time_id2, SHIP_COUNT from (

          select b.time_id time_id2, count(a.SHIP_DATE) SHIP_COUNT  from TEMP_ORDER a, R0000_CALENDAR b

          where (b.TIME_ID  = a.SHIP_DATE -- Outer join in Oracle

          and b.time_id between to_date('010111','ddmmyy') and to_date('311211','ddmmyy')

          group by b.TIME_ID) b ),

           

          (select time_id3, open_count from (

          WITH DAYS AS

          (select b.time_id D from R0000_CALENDAR b where  b.time_id between to_date('010111','ddmmyy') and to_date('311211','ddmmyy'))

          SELECT

              DAYS.D time_id3,

              SUM(CASE

                  WHEN B.Ship_date IS NULL THEN

                      0

                  ELSE

                      1

                  END) Open_Count

          FROM

              DAYS

              LEFT OUTER JOIN TEMP_ORDER B

              ON (DAYS.D BETWEEN B.order_date AND NVL(b.ship_date, DAYS.D))

              AND B.order_date between to_date('010111','ddmmyy') and to_date('311211','ddmmyy')

          GROUP BY DAYS.D

          ORDER BY DAYS.D ASC) c)

           

          WHERE 1=1

          and time_id1 = time_id2

          and time_id2 = time_id3;

           

           

          The output is in attached format export_order_ship_open.xlsx

          and the "R0000_CALENDAR" file contains a time dimension table.

          And sample data in excel.

           

          Drawbacks: it solves the purpose but doesn't help with analytic.

           

          Can someone help with converting the query into Tableau?